如何在sql server中将字符串拆分成多个字符串

Question

我有一个列数据，如下所示，

126-35-56-24

我想要的结果是，

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

我只是尝试使用substring，我可以将字符串拆分为126和35-56-24，但无法得到我想要的结果。

请帮我拿一下这个。先谢谢你...

Answer 1

使用下面的脚本，它将根据字符索引'-‘拆分字符串。

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'

示例输出：

​

​

Answer 2

如果总是有4组数据，你可以使用parsename。

我只是简单地将-替换为。让parsename来做剩下的事情。

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

Answer 3

使用以下脚本：

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

对于拆分：

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