如何在Sql Server中拆分列值 [英] How to Split a Column value in Sql Server

问题描述

我在1,2,3这样的表中有一个列值。我想在sql server中将它分成三行，

如果有人知道让我知道

i have a column value in a table like 1,2,3. i want to split it into three rows in sql server,
if anyone knows Let me know

推荐答案

试试这个..为此解开在sql :)

try this.. unpivot for that in sql :)

SELECT * FROM (
SELECT [column1] ,[column2] ,[column3] FROM [dbo].[table1]) T
UNPIVOT ( Value FOR DataValue IN ( [column1],[column2] ,[column3] ))P

你可以这样做：

Hi,

You can do it like this:

DECLARE @columnValue VARCHAR(MAX) = '1,2,3';
DECLARE @xmlList XML = CAST('<i>' + REPLACE(@columnValue, ',', '</i><i>') + '</i>' AS XML);

SELECT x.i.value('.', 'VARCHAR(MAX)') AS [Values]
FROM @xmlList.nodes('i') x(i);

结果：

Result:

Values
1
2
3

我建议你阅读这篇文章：任意字符串网球？ [ ^ ]

试试这个，

Try This,

CREATE FUNCTION [Split]
   (
       @String varchar(8000),
       @Delimiter char(1)
   )
   returns @temptable TABLE (items varchar(8000))
   as
   begin
       declare @idx int
       declare @slice varchar(8000)

       select @idx = 1
           if len(@String)<1 or @String is null  return

       while @idx!= 0
       begin
           set @idx = charindex(@Delimiter,@String)
           if @idx!=0
               set @slice = left(@String,@idx - 1)
           else
               set @slice = @String

           if(len(@slice)>0)
               insert into @temptable(Items) values(@slice)

           set @String = right(@String,len(@String) - @idx)
           if len(@String) = 0 break
       end
   return
   end

和

And

SELECT * from Split('1,2,3',',')

希望这对你有帮助。

Hope this will help you.

这篇关于如何在Sql Server中拆分列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！