CODEWITHSUNDEEP
sqlsql-serverstring
ghDev
ghDev

Reputation: 180

Split string by whitespace and select second part SQL

I have written a query which returns a table with one column and one row.

Example:

+---------------+
| playerID      | 
+---------------+
| Adam Overflow |            
+---------------+

Desired Result I want to select the second part of that name from that table so the output is Overflow.

I have tried to figure out the STRING_SPLIT function, but I cannot seem to get it to work. This is what I have tried:

SELECT value FROM STRING_SPLIT(
SELECT playerID FROM Players, ' ');

And the Players table is as described. The error I get is:

Syntax error in FROM clause

I realize this should be relatively simple and there might be a duplicate answer on here, but again I cannot seem to figure it out. Thanks for any assistance.

Upvotes: 0

Views: 8373

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can approach this using string_split():

SELECT value
FROM players p CROSS APPLY
     STRING_SPLIT(p.playerID, ' ') s
WHERE p.playerID LIKE ' ' + s.value + '%';

Upvotes: 0

Gnyasha
Gnyasha

Reputation: 684

This should bring the required result:

SELECT SUBSTRING( a.TheData , LEN(a.TheData ) -  CHARINDEX(' ',REVERSE(a.TheData )) + 2  , LEN(a.TheData )  ) results FROM (select 'playerID' as TheData
union all
select 'Adam Overflow'
union all
select 'Adam Overflow Meta') a;

Upvotes: 0

GMB
GMB

Reputation: 222432

string_split() is not the right tool for this job; it is a set-returning functions (it create rows), and does not guarantee ordering whatsoever.

You can just use scalar string functions:

select 
    playerID, 
    substring(playerID, charindex(' ', playerID) + 1, len(playerID)) as shortID
from players

right() also comes to mind:

right(playerID, len(playerID) - charindex(' ', playerID)) as shortID

Demo on DB Fiddle:

select 
    playerID, 
    substring(playerID, charindex(' ', playerID) + 1, len(playerID)) shortID1,
    right(playerID, len(playerID) - charindex(' ', playerID)) shortID2
from (values ('Adam Overflow')) as t(playerID)
GO

playerID      | shortID1 | shortID2
:------------ | :------- | :-------
Adam Overflow | Overflow | Overflow

Upvotes: 4

Related Questions