'Split Alpha And Numeric Using SQL Separate in Columns
I have records that that in some times came with one text and one number and sometimes two, three or four and they are separated with two point ':'. i want to split the numeric and the text in multiple column.
Here is a record example: March : 1 April : 2 May : 10 June : 1
desired result :
| Str_1 | item_1 | Str_2 | item_2 | Str_3 | item_3 | Str_4 | item_4 |
|---|---|---|---|---|---|---|---|
| March | 1 | April | 2 | May | 10 | June | 1 |
I'm using SQL SERVER 2012
i made this script but it doesn't work with all the cases
select substring(@str,1,charindex(':',@str)-1) as str_Type,
case when len(@str)-len(replace(@str,':',''))=1 then substring(@str,charindex(':',@str)+1,len(@str))
else substring(ltrim(
SUBSTRING(
substring(replace
(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,1
,CHARINDEX(':',substring(replace
(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,
LEN(replace(@str,'-',''))))-1))
,1
,charindex(' ',ltrim(SUBSTRING(
substring(replace(
@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,1
,CHARINDEX(':',substring(replace
(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,
LEN(replace(@str,'-',''))))-1)))-1)
end as str_Unit,
case when len(@str)-len(replace(@str,':',''))=1 then null
else substring(ltrim(SUBSTRING(
substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-','')))
,1
,CHARINDEX(':',substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))-1))
,charindex(' ',ltrim(SUBSTRING(substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))) ,1,CHARINDEX(':',substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))-1)))+1
,len(ltrim(SUBSTRING(substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))) ,1,CHARINDEX(':',substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))-1))))
end as str_type_2 ,
LEFT(case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-','')))))
End, PATINDEX('%[0-9][^0-9]%', case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-','')))))
End )) as str_Unit_2,
case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,len(ltrim(SUBSTRING(substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))) ,1,CHARINDEX(':',substring(replace(@str,'-',''),CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))-1))))))
End as str_type_3,
reverse(LEFT(reverse(LEFT(case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9][^0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end, PATINDEX('%[0-9][^0-9]%', case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9][^0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end ))), PATINDEX('%[0-9][^0-9]%', reverse(LEFT(case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9][^0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end, PATINDEX('%[0-9][^0-9]%', case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9][^0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end ))) ))) str_Unit_3,
replace(LTRIM(RIGHT( case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end, LEN( case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end) - PATINDEX('%[0-9][^0-9]%', case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end ))),reverse(LEFT(reverse(LTRIM(RIGHT( case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end, LEN( case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end) - PATINDEX('%[0-9][^0-9]%', case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end )))), PATINDEX('%[0-9][^0-9]%', reverse(LTRIM(RIGHT( case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end, LEN( case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end) - PATINDEX('%[0-9][^0-9]%', case when len(@str)-len(replace(@str,':',''))=1 then null
else SUBSTRING(
substring(replace
(@str,'-',''),
PATINDEX('%[0-9]%',replace(@str,'-',''))+1,
LEN(replace(@str,'-','')))
,CHARINDEX(':',substring(replace(@str,'-',''),
CHARINDEX(':',replace(@str,'-',''))+1,LEN(replace(@str,'-',''))))+1,len(substring(replace(@str,'-','')
,CHARINDEX(':',replace(@str,'-',''))+1
,LEN(replace(@str,'-',''))))) end )))) )+2)),'') str_Type_4,
substring( @str,DATALENGTH(@str)-CHARINDEX(REVERSE(':'),REVERSE(@str))+3,len(@str)) as str_Unit_4
Thanks!
Solution 1:[1]
I'm not sure what that huge mess of code is supposed to do, but you can use OPENJSON with a bit of string-replace to split your text:
DECLARE @str varchar(max) = 'Text1 : 1 Text2 : 2 Text3 : 10 Text4 : 1';
SELECT
j.[key],
LEFT(j.value, v.pipe - 1),
SUBSTRING(j.value, v.pipe + 1, LEN(j.value))
FROM OPENJSON(
'["' +
REPLACE(
REPLACE(
@str,
' : ',
'|'
),
' ',
'","'
)
+ '"]'
) j
CROSS APPLY (VALUES (
CHARINDEX('|', j.value)
) ) v(pipe);
If you want it on one row you can aggregate it.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Charlieface |