Geek Answers Handbook

How to split a string into several in sql server

I have column data as below,

126-35-56-24

And I want the results to be so

 select 126 as Id, 35 as Age, 56 as EmpId, 24 as Day

I'm just trying to use substring , and I can split the string into 126 and 35-56-24, but I cannot get the result that I want.

Please help me get this. Thanks at Advance ...

0
source share
6 answers

Use below script which will split lines based on char '-' index.

 DECLARE @data varchar(50)='126-35-56-24' SELECT 'SELECT '+ SUBSTRING(@data,1,CHARINDEX('-', @data)-1) + ' as Id, ' +SUBSTRING(@data,CHARINDEX('-', @data)+1,CHARINDEX('-', @data,CHARINDEX('-',@data) + 1) -CHARINDEX('-', @data)-1)+ ' as Age, ' +SUBSTRING(@data,CHARINDEX('-', @data,CHARINDEX('-',@data)+1)+1,CHARINDEX('-', @data,CHARINDEX('-', @data,CHARINDEX('-',@data)+1)+1) -CHARINDEX('-', @data,CHARINDEX('-',@data)+1)-1)+' as EmpId, ' +SUBSTRING(@data,CHARINDEX('-', @data,CHARINDEX('-', @data,CHARINDEX('-',@data)+1)+1)+1,LEN(@data)-CHARINDEX('-', @data,CHARINDEX('-', @data,CHARINDEX('-',@data)+1)+1)+1) +' as Day'

Output Example:

enter image description here

+1
source

You can use parseame if it is always 4 datasets.

I just replaced a - with a. and let parsename do the rest.

 SELECT PARSENAME(Replace(Col,'-','.'), 1) AS 'Object Name' ID, PARSENAME(Replace(Col,'-','.'), 2) AS 'Age', PARSENAME(Replace(Col,'-','.'), 3) AS 'EmpID', PARSENAME(Replace(Col,'-','.'), 4) AS 'Day' FROM YOURTABLE
+5
source

Use below Script:

 DECLARE @VAR VARCHAR(50)='126-35-56-24' SELECT [1] AS ID, [2] AS AGE,[3] AS EMPID,[4] as day FROM (SELECT [1],[2],[3],[4] FROM (SELECT ID,VAL FROM Spliter(@VAR,'-')) AS B PIVOT (MAX(VAL) FOR ID IN ([1],[2],[3],[4]) ) AS A ) AS C

And for separation:

 CREATE FUNCTION Spliter ( @delimited nvarchar(max), @delimiter nvarchar(100) ) RETURNS @t TABLE ( id int identity(1,1), val nvarchar(max) ) AS BEGIN declare @xml xml set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r> </root>' insert into @t(val) select r.value('.','varchar(max)') as item from @xml.nodes('//root/r') as records(r) RETURN END GO
+3
source

Since no one mentioned this, you can simply use the PARSENAME function. It has been available in SQL Server for quite some time. It has some limitations, but it will handle the task in the example you specified.

+2
source

Everything Tim talked about ..,

But if you need a splitter

 Declare @String varchar(50) = '126-35-56-24' Select ID = max(case when Key_PS=1 then Key_Value else null end) ,Age = max(case when Key_PS=2 then Key_Value else null end) ,EmpID = max(case when Key_PS=3 then Key_Value else null end) ,Day = max(case when Key_PS=4 then Key_Value else null end) From [dbo].[udf-Str-Parse](@String,'-')

Returns

 ID Age EmpID Day 126 35 56 24

UDF

 CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10)) --Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',') -- Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ') Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max)) As Begin Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML) Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String) Return End
+1
source

If you are using SQL Server 2016, you can use the string_split function

 declare @str varchar(20) = '126-35-56-24' select id = max(case when rn=1 then value else null end) ,age = max(case when rn=2 then value else null end) ,empId = max(case when rn=3 then value else null end) ,[Day] = max(case when rn=4 then value else null end) from (select row_number() over(order by (select 1)) as rn, value from string_split(@str, '-')) a
+1
source

More articles:


All Articles