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

Solution 1

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

Solution 2

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.

Solution 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;

Comments

[Rafal]

this can change order of items

[Shweta Ranpise]

@Rafal Yes, So that we can pick first record.

[Santosh Jadi]

when i'm using with 3, its showing Name1

[Rafal]

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

[Chanukya]

@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

[Shweta Ranpise]

Oh yes, I saw that comment now. I posted this answer before that comment. @Rafal

[Ravi Makwana]

Is there any way to dot(.) in to special character in context?