SQL select to get a string between two spaces

Question

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?

Answer 1

One way:

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

Answer 2

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)

Answer 3

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