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 11 · Accepted Answer · answered Jun 14 '18 at 07:18

11

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,073
17
60
64

score 11 · Answer 2 · edited Oct 24 '19 at 07:58

11

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,333
1
25
38

answered Jun 14 '18 at 07:21

Chanukya

5,685
1
18
35

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

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

2

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

92
7

2

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
1

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