score:11

Accepted answer

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

Read More

score: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;

score: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.


More questions

More questions with similar tag