Split and get second row as value

Question

I'm using below query to split the names as shown below:

select value from STRING_SPLIT('Name1~Name2~Name3' , '~' );

How to get second name i.e, Name2, without using WHERE condition?

Note: Names can be dynamic

@PatrickArtner: Not duplicate, need to achieve it without WHERE condition. — Santosh Jadi, Jun 14 '18 at 07:15
You need the 2nd value respecting the same order from the string before the split? — EzLo, Jun 14 '18 at 07:16
Regrettfully `STRING_SPLIT()` does not return the position / index. There is no safe way to get an element by the position with `STRING_SPLIT` — Shnugo, Jun 14 '18 at 08:06
There is a way: SELECT value FROM (SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) idx FROM STRING_SPLIT('Name1~Name2~Name3', '~') ) t WHERE idx=2 — tibx, Oct 28 '20 at 21:00

score 14 · Answer 1 · edited Oct 24 '19 at 07:58

14

Try PARSENAME function

SELECT PARSENAME( REPLACE('Name1~Name2~Name3','~','.'),2)

output

Name2

PARSENAME Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name.

edited Oct 24 '19 at 07:58

Ravi Makwana

2,782
1
29
41

answered Jun 14 '18 at 07:21

Chanukya

5,833
1
22
36

1

when i'm using with 3, its showing `Name1` – Santosh Jadi Jun 14 '18 at 07:28
@SantoshJadi actually it will accept only 4 values servername.databasename.shemaname.tablename so read about parsename you will come to know about this function based on value we should write the number – Chanukya Jun 14 '18 at 07:30
Is there any way to dot(.) in to special character in context? – Ravi Makwana Oct 24 '19 at 04:56
1

This will work only for maximum 4 blocks. – vermachint Aug 09 '22 at 05:13

score 12 · Accepted Answer · answered Jun 14 '18 at 07:18

12

Instead of using STRING_SPLIT you can convert your string to XML and then use .value to retrieve the 2nd element:

SELECT CAST('<t>' + REPLACE('Name1~Name2~Name3' , '~','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)')

answered Jun 14 '18 at 07:18

Andrea

11,801
17
65
72

1

This is actually the correct answer. Parsename has some caveats allowing for wrong values seeping in. I didn't debug why this happened, but it was enough for me to conclude the fallacy. This, however, is perfect. – BNazaruk Dec 06 '22 at 23:07

score 3 · Answer 3 · answered Jun 14 '18 at 07:23

3

Try below code:

SELECT TOP 1 T.* FROM 
(SELECT TOP 2 * FROM STRING_SPLIT('Name1~Name2~Name3' , '~' ) ORDER BY value ASC) AS T
ORDER BY value DESC;

answered Jun 14 '18 at 07:23

Shweta Ranpise

102
7

4

this can change order of items – Rafal Jun 14 '18 at 07:24
@Rafal Yes, So that we can pick first record. – Shweta Ranpise Jun 14 '18 at 07:27
2

as stated in question you need to select second row and values are not necessarily in order in that string. So you answer will not work work all cases – Rafal Jun 14 '18 at 07:29
Oh yes, I saw that comment now. I posted this answer before that comment. @Rafal – Shweta Ranpise Jun 14 '18 at 09:43

Split and get second row as value

3 Answers3

Linked

Related