如何将逗号分隔的值拆分为列

我遇到了一个类似的问题，但一个复杂的，因为这是第一个线程，我发现有关的问题，我决定张贴我的发现。我知道这是一个简单问题的复杂解决方案，但我希望我可以帮助其他人谁去这个线程寻找一个更复杂的解决方案。我必须分割一个包含5个数字的字符串(列名: levelsFeed) ，并在一个单独的列中显示每个数字。 例如: 8,1,2,2,2 应显示如下:

1  2  3  4  5
-------------
8  1  2  2  2

解决方案1: 使用 XML 函数: 这个解决方案是迄今为止最慢的解决方案

SELECT Distinct FeedbackID,
, S.a.value('(/H/r)[1]', 'INT') AS level1
, S.a.value('(/H/r)[2]', 'INT') AS level2
, S.a.value('(/H/r)[3]', 'INT') AS level3
, S.a.value('(/H/r)[4]', 'INT') AS level4
, S.a.value('(/H/r)[5]', 'INT') AS level5
FROM (
SELECT *,CAST (N'<H><r>' + REPLACE(levelsFeed, ',', '</r><r>')  + '</r> </H>' AS XML) AS [vals]
FROM Feedbacks
)  as d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

解决方案2: 使用拆分函数和 pivot (拆分函数将字符串拆分为列名为 Data 的行)

SELECT FeedbackID, [1],[2],[3],[4],[5]
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY feedbackID ORDER BY (SELECT  null)) as rn
FROM (
SELECT FeedbackID, levelsFeed
FROM Feedbacks
) as a
CROSS APPLY dbo.Split(levelsFeed, ',')
) as SourceTable
PIVOT
(
MAX(data)
FOR rn IN ([1],[2],[3],[4],[5])
)as pivotTable

解决方案3: 使用字符串操作函数——比解决方案2快一点

SELECT FeedbackID,
SUBSTRING(levelsFeed,0,CHARINDEX(',',levelsFeed)) AS level1,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),4) AS level2,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),3) AS level3,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),2) AS level4,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),1) AS level5
FROM Feedbacks

因为 levelsFeed 包含5个字符串值，我需要为第一个字符串使用子字符串函数。

我希望我的解决方案能够帮助其他进入这个线程的人寻找更复杂的分列方法

使用字符串函数:)

select Value,
substring(String,1,instr(String," ") -1) Fname,
substring(String,instr(String,",") +1) Sname
from tablename;

用了两个函数,
1. substring(string, position, length) = = > 将字符串从一个位置返回到另一个长度
2. instr(string,pattern) = = > 返回图案的位置。

如果我们不在子字符串中提供长度参数，它将返回到字符串的结尾

CREATE FUNCTION [dbo].[fnSplit](@sInputList VARCHAR(8000), @sDelimiter VARCHAR(8000) = ',')
RETURNS @List TABLE (item VARCHAR(8000))
BEGIN


DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN


SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList,0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter),LEN(@sInputList))))


-- Indexes to keep the position of searching
IF LEN(@sItem) > 0


INSERT INTO @List SELECT @sItem


END


IF LEN(@sInputList) > 0
BEGIN


INSERT INTO @List SELECT @sInputList -- Put the last item in


END


RETURN


END

我认为 PARSENAME 是这个示例中使用的简洁的函数，正如本文中所描述的: http://www.sqlshack.com/parsing-and-rotating-delimited-data-in-sql-server-2012/

PARSENAME 函数的逻辑设计是解析由四部分组成的对象名称。PARSENAME 的优点在于它不仅仅局限于解析由四部分组成的 SQL Server 对象名称——它还可以解析任何由点分隔的函数或字符串数据。

第一个参数是要解析的对象，第二个参数是要返回的对象片段的整数值。本文讨论了解析和旋转分隔数据公司的电话号码，但也可用于解析姓名数据。

例如:

USE COMPANY;
SELECT PARSENAME('Whatever.you.want.parsed',3) AS 'ReturnValue';

本文还描述了如何使用名为“ replaceChars”的公共表表达式(Common Table Expression，CTE)对分隔符替换的值运行 PARSENAME。CTE 对于返回临时视图或结果集非常有用。

之后，UNPIVOT 函数被用来将一些列转换成行; SUBSTRING 和 CHARINDEX 函数被用来清除数据中的不一致性，LAG 函数(SQL Server 2012新增)最终被使用，因为它允许引用以前的记录。

试试这个:

declare @csv varchar(100) ='aaa,bb,csda,daass';
set @csv = @csv+',';


with cte as
(
select SUBSTRING(@csv,1,charindex(',',@csv,1)-1) as val, SUBSTRING(@csv,charindex(',',@csv,1)+1,len(@csv)) as rem
UNION ALL
select SUBSTRING(a.rem,1,charindex(',',a.rem,1)-1)as val, SUBSTRING(a.rem,charindex(',',a.rem,1)+1,len(A.rem))
from cte a where LEN(a.rem)>=1
) select val from cte

它是如此简单，你可以通过以下查询:

DECLARE @str NVARCHAR(MAX)='ControlID_05436b78-04ba-9667-fa01-9ff8c1b7c235,3'
SELECT LEFT(@str, CHARINDEX(',',@str)-1),RIGHT(@str,LEN(@str)-(CHARINDEX(',',@str)))

ALTER FUNCTION [dbo].[StringListTo] (@StringList Nvarchar(max),@Separators char(1),@start int, @index int )
RETURNS nvarchar(max)
AS
BEGIN
declare @out Nvarchar(max)
declare @i int
declare @start_old int
set @start=@start+1
set @i=1
while(@i<=@index)
begin
set @start_old=@start
set @start=CHARINDEX('.',@StringList,@start+1)
if (@start>0)
begin
set @out=Substring(@StringList,@start_old+1,@start-@start_old-1)
end
else
begin
set @out=Substring(@StringList,@start_old+1,len(@StringList)-1)
end
set @i=@i+1
end
RETURN @out
END;

DECLARE @INPUT VARCHAR (MAX)='N,A,R,E,N,D,R,A'
DECLARE @ELIMINATE_CHAR CHAR (1)=','
DECLARE @L_START INT=1
DECLARE @L_END INT=(SELECT LEN (@INPUT))
DECLARE @OUTPUT CHAR (1)


WHILE @L_START <=@L_END
BEGIN
SET @OUTPUT=(SUBSTRING (@INPUT,@L_START,1))
IF @OUTPUT!=@ELIMINATE_CHAR
BEGIN
PRINT @OUTPUT
END
SET @L_START=@L_START+1
END

使用 SQLServer2016，我们可以使用 string _ split 来完成以下任务:

create table commasep (
id int identity(1,1)
,string nvarchar(100) )


insert into commasep (string) values ('John, Adam'), ('test1,test2,test3')


select id, [value] as String from commasep
cross apply string_split(string,',')

select distinct modelFileId,F4.*
from contract
cross apply (select XmlList=convert(xml, '<x>'+replace(modelFileId,';','</x><x>')+'</x>').query('.')) F2
cross apply (select mfid1=XmlNode.value('/x[1]','varchar(512)')
,mfid2=XmlNode.value('/x[2]','varchar(512)')
,mfid3=XmlNode.value('/x[3]','varchar(512)')
,mfid4=XmlNode.value('/x[4]','varchar(512)') from XmlList.nodes('x') F3(XmlNode)) F4
where modelFileId like '%;%'
order by modelFileId

尝试这样做(更改“ to”的实例或您想使用的任何分隔符)

CREATE FUNCTION dbo.Wordparser
(
@multiwordstring VARCHAR(255),
@wordnumber      NUMERIC
)
returns VARCHAR(255)
AS
BEGIN
DECLARE @remainingstring VARCHAR(255)
SET @remainingstring=@multiwordstring


DECLARE @numberofwords NUMERIC
SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, ' ', '')) + 1)


DECLARE @word VARCHAR(50)
DECLARE @parsedwords TABLE
(
line NUMERIC IDENTITY(1, 1),
word VARCHAR(255)
)


WHILE @numberofwords > 1
BEGIN
SET @word=LEFT(@remainingstring, CHARINDEX(' ', @remainingstring) - 1)


INSERT INTO @parsedwords(word)
SELECT @word


SET @remainingstring= REPLACE(@remainingstring, Concat(@word, ' '), '')
SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, ' ', '')) + 1)


IF @numberofwords = 1
BREAK


ELSE
CONTINUE
END


IF @numberofwords = 1
SELECT @word = @remainingstring
INSERT INTO @parsedwords(word)
SELECT @word


RETURN
(SELECT word
FROM   @parsedwords
WHERE  line = @wordnumber)


END

示例用法:

SELECT dbo.Wordparser(COLUMN, 1),
dbo.Wordparser(COLUMN, 2),
dbo.Wordparser(COLUMN, 3)
FROM   TABLE

这招对我很管用

CREATE FUNCTION [dbo].[SplitString](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE ( val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT  r.value('.','varchar(MAX)') as item
FROM  @xml.nodes('/t') as records(r)
RETURN
END

可以使用拆分函数。

SELECT
(select top 1 item from dbo.Split(FullName,',') where id=1 ) as Name,
(select top 1 item from dbo.Split(FullName,',') where id=2 ) as Surname,
FROM MyTbl

Select distinct PROJ_UID,PROJ_NAME,RES_UID from E2E_ProjectWiseTimesheetActuals
where   CHARINDEX(','+cast(PROJ_UID as varchar(8000))+',', @params) > 0 and  CHARINDEX(','+cast(RES_UID as varchar(8000))+',', @res) > 0

您可以使用 桌面价值函数 STRING_SPLIT，该函数仅在兼容级别130下可用。如果数据库兼容级别低于130，SQLServer 将无法查找和执行 STRING_SPLIT函数。可以使用以下命令更改数据库的兼容级别:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

语法

SELECT * FROM STRING_SPLIT ( string, separator )

请参阅这里的文档

我认为以下功能将为您工作:

您必须首先在 SQL 中创建一个函数

CREATE FUNCTION [dbo].[fn_split](
@str VARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @returnTable TABLE (idx INT PRIMARY KEY IDENTITY, item VARCHAR(8000))
AS
BEGIN
DECLARE @pos INT
SELECT @str = @str + @delimiter
WHILE LEN(@str) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimiter,@str)
IF @pos = 1
INSERT @returnTable (item)
VALUES (NULL)
ELSE
INSERT @returnTable (item)
VALUES (SUBSTRING(@str, 1, @pos-1))
SELECT @str = SUBSTRING(@str, @pos+1, LEN(@str)-@pos)
END
RETURN
END

你可以这样调用这个函数:

select * from fn_split('1,24,5',',')

实施方法:

Declare @test TABLE (
ID VARCHAR(200),
Data VARCHAR(200)
)


insert into @test
(ID, Data)
Values
('1','Cleo,Smith')




insert into @test
(ID, Data)
Values
('2','Paul,Grim')


select ID,
(select item from fn_split(Data,',') where idx in (1)) as Name ,
(select item from fn_split(Data,',') where idx in (2)) as Surname
from @test

结果会是这样的:

这个函数是最快的:

CREATE FUNCTION dbo.F_ExtractSubString
(
@String VARCHAR(MAX),
@NroSubString INT,
@Separator VARCHAR(5)
)
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @St INT = 0, @End INT = 0, @Ret VARCHAR(MAX)
SET @String = @String + @Separator
WHILE CHARINDEX(@Separator, @String, @End + 1) > 0 AND @NroSubString > 0
BEGIN
SET @St = @End + 1
SET @End = CHARINDEX(@Separator, @String, @End + 1)
SET @NroSubString = @NroSubString - 1
END
IF @NroSubString > 0
SET @Ret = ''
ELSE
SET @Ret = SUBSTRING(@String, @St, @End - @St)
RETURN @Ret
END
GO

示例用法:

SELECT dbo.F_ExtractSubString(COLUMN, 1, ', '),
dbo.F_ExtractSubString(COLUMN, 2, ', '),
dbo.F_ExtractSubString(COLUMN, 3, ', ')
FROM   TABLE

试试以下方法:

USE TRIAL
GO
CREATE TABLE DETAILS
(
ID INT,
NAME VARCHAR(50),
ADDRESS VARCHAR(50)
)


INSERT INTO DETAILS
VALUES (100, 'POPE-JOHN-PAUL','VATICAN CIT|ROME|ITALY')
,(240, 'SIR-PAUL-McARTNEY','NEWYORK CITY|NEWYORK|USA')
,(460,'BARRACK-HUSSEIN-OBAMA','WHITE HOUSE|WASHINGTON|USA')
,(700, 'PRESIDENT-VLADAMIR-PUTIN','RED SQUARE|MOSCOW|RUSSIA')
,(950, 'NARENDRA-DAMODARDAS-MODI','10 JANPATH|NEW DELHI|INDIA')

质疑:

select [ID]
,[NAME]
,[ADDRESS]
,REPLACE(LEFT(NAME, CHARINDEX('-', NAME)),'-',' ') as First_Name
,CASE
WHEN CHARINDEX('-',REVERSE(NAME))+ CHARINDEX('-',NAME) < LEN(NAME)
THEN  SUBSTRING(NAME, CHARINDEX('-', (NAME)) + 1, LEN(NAME) - CHARINDEX('-', REVERSE(NAME)) - CHARINDEX('-', NAME))
ELSE 'NULL'
END AS Middle_Name
,REPLACE(REVERSE( SUBSTRING( REVERSE(NAME), 1, CHARINDEX('-',REVERSE(NAME)))), '-','') AS Last_Name
,REPLACE(LEFT(ADDRESS, CHARINDEX('|', ADDRESS)),'|',' ') AS Locality
,CASE
WHEN CHARINDEX('|',REVERSE(ADDRESS))+ CHARINDEX('|',ADDRESS) < LEN(ADDRESS)
THEN SUBSTRING(ADDRESS, CHARINDEX('|', (ADDRESS))+1, LEN(ADDRESS)-CHARINDEX('|', REVERSE(ADDRESS))-CHARINDEX('|',ADDRESS))
ELSE 'Null'
END AS STATE
,REPLACE(REVERSE(SUBSTRING(REVERSE(ADDRESS),1 ,CHARINDEX('|',REVERSE(ADDRESS)))),'|','') AS Country
FROM DETAILS


SELECT CHARINDEX('-', REVERSE(NAME)) AS LAST,CHARINDEX('-',NAME)AS FIRST, LEN(NAME) AS LENGTH
FROM DETAILS


SELECT SUBSTRING(NAME, CHARINDEX('-', (NAME))+1, LEN(NAME) -CHARINDEX('-', REVERSE(NAME)) - CHARINDEX('-', NAME))
FROM DETAILS

如果你对密码有任何疑问，请告诉我

ALTER function get_occurance_index(@delimiter varchar(1),@occurence int,@String varchar(100))
returns int
AS Begin
--Declare @delimiter varchar(1)=',',@occurence int=2,@String varchar(100)='a,b,c'
Declare @result int
;with T as (
select 1 Rno,0 as row, charindex(@delimiter, @String) pos,@String st
union all
select Rno+1,pos + 1, charindex(@delimiter, @String, pos + 1), @String
from T
where pos > 0
)
select  @result=pos
from T
where pos > 0   and rno = @occurence
return isnull(@result,0)
ENd




declare @data as table (data varchar(100))
insert into @data values('1,2,3')
insert into @data values('aaa,bbbbb,cccc')
select top  3 Substring (data,0,dbo.get_occurance_index( ',',1,data)) ,--First Record always starts with 0
Substring (data,dbo.get_occurance_index( ',',1,data)+1,dbo.get_occurance_index( ',',2,data)-dbo.get_occurance_index( ',',1,data)-1) ,
Substring (data,dbo.get_occurance_index( ',',2,data)+1,len(data)) , -- Last record cant be more than len of actual data
data
From @data

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0


SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END


WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)


INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]


SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END


RETURN
END

我重写了上面的一个答案，并做得更好:

CREATE FUNCTION [dbo].[CSVParser]
(
@s        VARCHAR(255),
@idx      NUMERIC
)
RETURNS VARCHAR(12)
BEGIN
DECLARE @comma int
SET @comma = CHARINDEX(',', @s)
WHILE 1=1
BEGIN
IF @comma=0
IF @idx=1
RETURN @s
ELSE
RETURN ''


IF @idx=1
BEGIN
DECLARE @word VARCHAR(12)
SET @word=LEFT(@s, @comma - 1)
RETURN @word
END


SET @s = RIGHT(@s,LEN(@s)-@comma)
SET @comma = CHARINDEX(',', @s)
SET @idx = @idx - 1
END
RETURN 'not used'
END

示例用法:

SELECT dbo.CSVParser(COLUMN, 1),
dbo.CSVParser(COLUMN, 2),
dbo.CSVParser(COLUMN, 3)
FROM   TABLE

问题很简单，但问题是热点:)

因此，我为 String _ split ()创建了一些包装器，支点以更通用的方式产生了这些包装器。它是返回值(nn，value1，value2，... ，value50)的 table 函数——对于大多数 CSV 行来说足够了。如果有更多的值，它们将换行到下一行-你好表示行号。将第三个参数 @ columnCnt = [ yourNumber ]设置为在特定位置换行:

alter FUNCTION fn_Split50
(
@str varchar(max),
@delim char(1),
@columnCnt int = 50
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM (SELECT
nn = (nn - 1) / @columnCnt + 1,
nnn = 'value' + cast(((nn - 1) % @columnCnt) + 1 as varchar(10)),
value
FROM (SELECT
nn = ROW_NUMBER() over (order by (select null)),
value
FROM string_split(@str, @delim) aa
) aa
where nn > 0
) bb
PIVOT
(
max(value)
FOR nnn IN (
value1, value2, value3, value4, value5, value6, value7, value8, value9, value10,
value11, value12, value13, value14, value15, value16, value17, value18, value19, value20,
value21, value22, value23, value24, value25, value26, value27, value28, value29, value30,
value31, value32, value33, value34, value35, value36, value37, value38, value39, value40,
value41, value42, value43, value44, value45, value46, value47, value48, value49, value50
)
) AS PivotTable
)

使用示例:

select * from dbo.fn_split50('zz1,aa2,ss3,dd4,ff5', ',', DEFAULT)

select * from dbo.fn_split50('zz1,aa2,ss3,dd4,ff5,gg6,hh7,jj8,ww9,qq10', ',', 3)

select * from dbo.fn_split50('zz1,11,aa2,22,ss3,33,dd4,44,ff5,55,gg6,66,hh7,77,jj8,88,ww9,99,qq10,1010', ',',2)

希望，这会有所帮助:)

可以使用 SQLServerSTRING_SPLIT函数:

STRING_SPLIT ( string , separator )

试试这个

CREATE FUNCTION [dbo].[Split]
(
@ListOfValues varchar(max),
@ValueSeparator varchar(10)
)
RETURNS @ListOfValuesInRows TABLE
(
Value varchar(max)
)
AS
BEGIN


IF Len(@ListOfValues) = 0
RETURN


if @ValueSeparator <> ' '
Begin
WHILE CHARINDEX(@ValueSeparator, @ListOfValues) > 0
BEGIN


INSERT INTO @ListOfValuesInRows
SELECT LTRIM(RTRIM(SUBSTRING(@ListOfValues, 1, CHARINDEX(@ValueSeparator, @ListOfValues)-1)))


SET @ListOfValues = SubString(@ListOfValues, CharIndex(@ValueSeparator, @ListOfValues)+Len(@ValueSeparator), Len(@ListOfValues))


END


INSERT INTO @ListOfValuesInRows
SELECT LTRIM(RTRIM(@ListOfValues))
End
Else
BEGIN
DECLARE @xml XML;
SET @xml = N'<t>' + REPLACE(@ListOfValues, @ValueSeparator, '</t><t>') + '</t>';
INSERT INTO @ListOfValuesInRows (Value)
SELECT LTRIM(RTRIM(r.value( '.', 'varchar(MAX)' ))) AS item
FROM @xml.nodes( '/t' ) AS records( r )


END


RETURN


END

这是一个老问题，但是如果有可能升级到 SQLServer2017 + ，基于 JSON 的方法也是一种选择。我们的想法是进行适当的转变:

• 将存储在 String列中的文本转换为有效的 JSON 数组(Cleo, Smith转换为 ["Cleo"," Smith"]) ，并使用 JSON_VALUE()解析该数组。

• 将存储在 String列中的文本转换为有效的嵌套 JSON 数组(Cleo, Smith转换为 [["Cleo"," Smith"]]) ，并使用 OPENJSON()和显式模式(列定义)解析该数组。

表:

SELECT [Value], [String]
INTO Data
FROM (VALUES
(1, 'Cleo, Smith'),
(2, 'John, Smith'),
(3, 'Marian')
) v ([Value], [String])

使用 JSON_VALUE()的语句:

SELECT
[Value],
TRIM(JSON_VALUE(CONCAT('["', REPLACE(STRING_ESCAPE([String], 'json'), ',', '","'), '"]'), 'lax $[0]')) AS Name,
TRIM(JSON_VALUE(CONCAT('["', REPLACE(STRING_ESCAPE([String], 'json'), ',', '","'), '"]'), 'lax $[1]')) AS Surname
FROM Data

使用 OPENJSON()的语句:

SELECT d.[Value], TRIM(j.[Name]) AS [Name], TRIM(j.[Surname]) AS [Surname]
FROM Data d
OUTER APPLY OPENJSON(CONCAT('[["', REPLACE(STRING_ESCAPE(d.[String], 'json'), ',', '","'), '"]]')) WITH (
Name varchar(100) 'lax $[0]',
Surname varchar(100) 'lax $[1]'
) j

结果:

Value  Name   Surname
---------------------
1      Cleo   Smith
2      John   Smith
3      Marian

另外，使用这种技术，您可以通过添加适当的 JSON path轻松地解析包含两列以上的文本。

我说了:

drop table if exists #test;
create table #test(valor varchar(200));
insert into #test values ('Cleo, Smith'), ('Jhon');
select
*
,REVERSE(PARSENAME(REPLACE(REVERSE(valor), ',', '.'), 1)) as name
,REVERSE(PARSENAME(REPLACE(REVERSE(valor), ',', '.'), 2)) as Surname
,REVERSE(PARSENAME(REPLACE(REVERSE(valor), ',', '.'), 3)) as other
from #test;


/*
+-----------+----+-------+-----+
|valor      |name|Surname|other|
+-----------+----+-------+-----+
|Cleo, Smith|Cleo| Smith |NULL |
|Jhon       |Jhon|NULL   |NULL |
+-----------+----+-------+-----+
*/