Get word before semicolon from string in SQL Server
I have the line below:
Consolidation CompletedThe Scenario is LDP; the Year is 2018; the Start Period is July; the End Period is June; the Entity is TOT_NEWS.
And you need to get the words before the symbol semicolon (;)
and the last word beforedot (.)
Result:
----- ---- ---- ---- ----
LDP 2018 July June TOT_NEWS
I could get the first one LDP
with the following choice:
REVERSE(LEFT(REVERSE(SUBSTRING(strDescription, CHARINDEX('The Scenario
is', strDescription,+39),
CHARINDEX(';',strDescription,-1))), CHARINDEX (' ',
REVERSE(SUBSTRING(strDescription,
CHARINDEX('The Scenario is', strDescription,+39),
CHARINDEX(';',strDescription,-1)))))) as Scenario
But it doesn't work for the rest of the line.
+3
source to share
3 answers
Here you go:
-- Defining your string
DECLARE @string varchar(max)
SET @string = 'Consolidation CompletedThe Scenario is LDP; the Year is 2018; the Start Period is July; the End Period is June; the Entity is TOT_NEWS.'
;with pos as (
SELECT
@string as String
,CHARINDEX(';', @string, 1) as Pos_1
,CHARINDEX(';', @string, CHARINDEX(';', @string, 1)+1) as Pos_2
,CHARINDEX(';', @string, CHARINDEX(';', @string, CHARINDEX(';', @string, 1)+1)+1) as Pos_3
,CHARINDEX(';', @string, CHARINDEX(';', @string, CHARINDEX(';', @string, CHARINDEX(';', @string, 1)+1)+1)+1) as Pos_4
) , txt as (
select
String
,substring(String, 1,Pos_1-1) as String_1
,substring(String,Pos_1+1,Pos_2-Pos_1-1) as String_2
,substring(String,Pos_2+1,Pos_3-Pos_2-1) as String_3
,substring(String,Pos_3+1,Pos_4-Pos_3-1) as String_4
,substring(String,Pos_4+1,LEN(String)-Pos_4-1) as String_5
from pos
)
SELECT
string
,substring(String_1,len(String_1)-charindex(' ',reverse(String_1),1)+1,charindex(' ',reverse(String_1))) as Result_1
,substring(String_2,len(String_2)-charindex(' ',reverse(String_2),1)+1,charindex(' ',reverse(String_2))) as Result_2
,substring(String_3,len(String_3)-charindex(' ',reverse(String_3),1)+1,charindex(' ',reverse(String_3))) as Result_3
,substring(String_4,len(String_4)-charindex(' ',reverse(String_4),1)+1,charindex(' ',reverse(String_4))) as Result_4
,substring(String_5,len(String_5)-charindex(' ',reverse(String_5),1)+1,charindex(' ',reverse(String_5))) as Result_5
from txt
Output:
string | Result_1 | Result_2 | Result_3 | Result_4 | Result_5
----------------------------------------------------------------------------------------------------------------------------------------|-----------|----------|----------|----------|---------
Consolidation CompletedThe Scenario is LDP; the Year is 2018; the Start Period is July; the End Period is June; the Entity is TOT_NEWS. | LDP | 2018 | July | June | TOT_NEWS
+1
source to share
Here's a way to use the string separator ...
declare @value varchar(max) = 'Consolidation CompletedThe Scenario is LDP; the Year is 2018; the Start Period is July; the End Period is June; the Entity is TOT_NEWS.'
;with cte as(
select
* from
dbo.DelimitedSplit8K(@value,';'))
select
replace(right(Item,charindex(' ',reverse(Item),1)),'.','')
from cte
FUNCTION
CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
+2
source to share
If you are open to TVF (table-valued function).
This approach uses a modified split / parse function. Instead of one delimeter, I use two dissimilar delimiters. In this case, a ' '
and';'
Example
Declare @YourTable table (id int,strDescription varchar(max))
Insert Into @YourTable values
(1,'Consolidation CompletedThe Scenario is LDP; the Year is 2018; the Start Period is July; the End Period is June; the Entity is TOT_NEWS.')
Select A.ID
,B.*
From @YourTable A
Cross Apply (
Select Pos1 = max(case when RetSeq=1 then RetVal end)
,Pos2 = max(case when RetSeq=2 then RetVal end)
,Pos3 = max(case when RetSeq=3 then RetVal end)
,Pos4 = max(case when RetSeq=4 then RetVal end)
,Pos5 = max(case when RetSeq=5 then RetVal end)
From [dbo].[udf-Str-Extract](A.strDescription+';',' ',';')
) B
Returns
ID Pos1 Pos2 Pos3 Pos4 Pos5
1 LDP 2018 July June TOT_NEWS
UDF if Interest
CREATE FUNCTION [dbo].[udf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By N)
,RetPos = N
,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1)
From (
Select *,RetVal = Substring(@String, N, L)
From cte4
) A
Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters
Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[udf-Str-Extract] (@String,'[[',']]')
*/
+2
source to share