How to split a string value in different columns without function

Question

We have this query where called a function with 2 values.

SELECT CampoValores, Numero FROM [dbo].[ObtenerDatosPropios] ('RE-00935898','Alvaro')

And this is the results:

Results

But we need to split "CampoValor" in different columns separeated with Semicolon(;)

Something like this:

Finish result

If it is possible we need to solve this recursively.

This smells like xml. https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column sound right? — Twelfth, Oct 18 '17 at 15:53

John Cappelletti · Accepted Answer · 2017-10-18T18:04:06.250

Here is an inline approach, provided you have a fixed or maximum number of columns.

Easy to expand or contract... the pattern is pretty clear.

Example

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values 
 (1,'Some;string;with;text')
,(2,'Another;string;with;some;more;text')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.SomeCol,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X 
             ) B

Returns

EDIT - For the Function

Select B.*
      ,Numero
 From  [dbo].[ObtenerDatosPropios] ('RE-00935898','Alvaro') A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.CampoValores,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X 
             ) B

EDIT 2 - Dynamic

Not tested, but this should work. We're basically doing some dynamic SQL within dynamic SQL.

Declare @SQL varchar(max) = '
Select A.Numero 
      ,Col = ''Pos''+cast(RetSeq as varchar(25))
      ,B.*
 Into  #Temp
 From  [dbo].[ObtenerDatosPropios] ('''+@P1+''','''+@P2+''') A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value(''(./text())[1]'', ''varchar(max)'')))
                From  (Select x = Cast(''<x>'' + replace((Select replace(A.CampoValores,'';'',''§§Split§§'') as [*] For XML Path('''')),''§§Split§§'',''</x><x>'')+''</x>'' as xml).query(''.'')) as A 
                Cross Apply x.nodes(''x'') AS B(i)
             ) B

Declare @SQL varchar(max) 
Set @SQL =   Stuff((Select Distinct '',''+quotename(Col) From #Temp Order By 1 For XML Path('''')),1,1,'''')
Set @SQL = ''
Select ''+@SQL+'',Numero
 From  (Select Col,RetVal,Numero From #Temp) A
 Pivot (max([RetVal]) For [Col] in ('' + @SQL + '') ) p''
Exec(@SQL)
'
--Print @SQL
Exec(@SQL)

@MatiasBlacher So change (at) YourTable to [dbo].[ObtenerDatosPropios] ('RE-00935898','Alvaro') — John Cappelletti, Oct 18 '17 at 16:29
@MatiasBlacher As you know, SQL Server is declarative by design. If you want a variable number of columns, you would have to go DYNAMIC. — John Cappelletti, Oct 18 '17 at 16:36
@MatiasBlacher Happy to help. Just let me know if you need a dynamic version — John Cappelletti, Oct 18 '17 at 16:43
@MatiasBlacher Apologies for the delay, I got pulled away ... See EDIT 2 — John Cappelletti, Oct 18 '17 at 18:04

How to split a string value in different columns without function

1 Answers1