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: Text1 : 1 Text2 : 2 Text3 : 10 Text4 : 1
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!
CodePudding user response：
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.
CodePudding user response：
thanks for your answer, how i can get like this, excuse me i'm a newbie
| Str_1 | item_1 | Str_2 | item_2 | Str_3 | item_3 | Str_4 | item_4 |
|---|---|---|---|---|---|---|---|
| Text1 | 1 | Text2 | 2 | Text3 | 10 | Text4 | 1 |