CODEWITHSUNDEEP
sql-servert-sqlsubstring
marky
marky

Reputation: 5068

SQL select to get a string between two spaces

I have a field with names in the format DOE JOHN HOWARD or DOE JOHN H.

I need a query to get the string between the two spaces (JOHN in this case).

The SO answer here shows how to do that when the desired substring is between two different strings, but I don't see how to apply something similar when the desired substring is between two instances of the same string (a space in this case).

How can I do that?

Upvotes: 3

Views: 23851

Answers (3)

Pawel Czapski
Pawel Czapski

Reputation: 1864

After doing some searches to resolve my problem, realised there is no generic answer, so below is piece of code to find string between some other strings. I think it may be useful for somebody in future.

DECLARE @STRING NVARCHAR(MAX) = 'Something here then stringBefore Searching stringAfter something there.'
DECLARE @FIRST NVARCHAR(20) = 'stringBefore'
DECLARE @SECOND NVARCHAR(20) = 'stringAfter'
DECLARE @SEARCHING NVARCHAR (20)

SET @SEARCHING = (SELECT SUBSTRING(@STRING, CHARINDEX(@FIRST, @STRING) + LEN(@FIRST), CHARINDEX(@SECOND, @STRING) - CHARINDEX(@FIRST, @STRING) - LEN(@FIRST))) 
-- if you want to remove empty spaces
SET @SEARCHING = REPLACE(@SEARCHING, ' ', '')
SELECT @SEARCHING

Then output as below:

(No column name)

Searching

Upvotes: 0

Scott
Scott

Reputation: 1228

There is a somewhat sneaky way you could do this using PARSENAME.

It's intended purpose is to get particular parts of an object/namespace, however, in this case you could use it by replacing the strings with periods first.

E.g.,

SELECT PARSENAME(REPLACE('DOE JOHN HOWARD',' ','.'),2)

Upvotes: 8

Alex K.
Alex K.

Reputation: 175766

One way:

select 
left(substring(fld, 
    charindex(' ', fld) + 1, len(fld)), 
    charindex(' ', substring(fld, charindex(' ', fld) + 2, len(fld))))

Upvotes: 8

Related Questions