0
declare @v varchar(max) = '1,11,111,1111'

I need output like this (in separate lines):

    1
   11
  111
 1111

I am able to get the first and the last using:

    select Substring(@v,0,CharIndex(',',@v)) 
    select substring(@v, CHARINDEX(',', @v, CHARINDEX(',', @v, CHARINDEX(',', @v)+1)+1)+1, len(@v))

But, how can I get the second and third substrings separately?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
money
  • 1
  • 2

1 Answers1

1

You can use replace...

declare @v varchar(max) = '1,11,111,1111' 
select replace(@v,',',' ')

If you are looking for multiple rows then 

--For SQL Server >= 2016
select * from string_split(@v,',')

--For any version of sql server
DECLARE @xml as xml
SET @xml = cast(('<X>'+replace(@v,',' ,'</X><X>')+'</X>') as xml)
SELECT  N.value('.', 'varchar(MAX)') as value FROM @xml.nodes('X') as T(N)
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38