Split string and select based on the row number-CodePudding

I've a string with multiple ids by separating with '.' mentioned below

'4400028285.4400043238.4400051619.4400052460.4400074441.4400124290.4400131680.4400137443 4400140030.4400141964.4400143906.4400143906.4400144145.4400149146'

Tried to parse the string and get individual records used PARSENAME() to parse the string but it allowing to get four to Five rows

as I coded below

PARSENAME('4400028285.4400043238.4400051619.4400052460.4400074441.4400124290.4400131680.4400137443 4400140030.4400141964.4400143906.4400143906.4400144145.4400149146', 6)

Can we use any other function to return the rows and select based on the row number?

CodePudding user response：

Please try the below query,

DECLARE @T VARCHAR(200) = '4400028285.4400043238.4400051619.4400052460.4400074441.4400124290.4400131680.4400137443 4400140030.4400141964.4400143906.4400143906.4400144145.4400149146'


SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Value) AS RowNum
,Value
FROM string_split(@T,'.')) A

CodePudding user response：

You can use json parsing for this

declare @string varchar(500)='4400028285.4400043238.4400051619.4400052460.4400074441.4400124290.4400131680.4400137443.4400140030.4400141964.4400143906.4400143906.4400144145.4400149146'

select [value]
from OpenJson(Concat('["', replace(@string, '.', '", "'), '"]'))
order by Convert(int, [key])