I'm Having a column data like below,
126-35-56-24
And I want the results be like,
select 126 as Id, 35 as Age, 56 as EmpId, 24 as Day
I just try using substring and I can Able to split the string into 126 and 35-56-24, but I can't get the result I Want.
Please help me to get this. Thanks in Advance...
You could use parsename if it's always 4 sets of data.
I simply replaced the - 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
Since no one else has mentioned it, you can simply use the PARSENAME function. It's been available in SQL Server for quite some time. It has some limitations, but it will handle the task in the example you gave.
Use the 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 splitting:
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
Use the below script ,which will split the strings based on the char index of '-'.
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'
Sample output :
Everything that Tim mentioned..,
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
The 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
If you are using SQL Server 2016 you can use 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
STRING_SPLITin SQL Server 2016 or later, otherwise you'll need a custom function or a really ugly query with substring and other stuff.