Split string and select based on the row number

Question

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?

If you try to transform the input text and parse it as JSON, a possible solution is: `DECLARE @text varchar(max) = '123.456.789.012.345.678.901'; SELECT JSON_VALUE('[' + REPLACE(@text, '.', ',') + ']', '$[5]')` — Zhorov, Sep 01 '22 at 06:07
And the duplicate Q&A: [Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string). — Zhorov, Sep 01 '22 at 06:20
PARSENAME function specificly used to separate "server_name.database_name.schema_name.tablename" structure, you might need to look at `STRING_SPLIT` function instead. — Helio, Sep 01 '22 at 06:31
have you looked at `select value from string_split(@test, '.')` where your string is in the varchar variable `@test` — GuidoG, Sep 01 '22 at 06:40
There is also no dot `.` between these 2 values `4400137443 4400140030` — GuidoG, Sep 01 '22 at 06:41
Also, note that on Azure SQL Database and SQL Server 2022, using `STRING_SPLIT()` with `enable_ordinal` parameter is probably the best option: `SELECT [value] FROM STRING_SPLIT(@text, '.', 1) WHERE [ordinal] = 6` — Zhorov, Sep 01 '22 at 06:45

score 1 · Answer 1 · answered Sep 01 '22 at 07:36

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

score 0 · Answer 2 · answered Sep 01 '22 at 07:43

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])

Split string and select based on the row number

2 Answers2