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?

In the case of George Herbert Walker Bush, what do you want to return? — Alex Weitzer, May 11 '15 at 22:59
@AlexWeitzer : Okay, there's no need to bring politics into this discussion - things are confusing enough as it is! :) — marky, May 12 '15 at 19:46

score 8 · Answer 1 · edited May 11 '15 at 17:18

8

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)

edited May 11 '15 at 17:18

AHiggins

7,029
6
36
54

answered May 11 '15 at 16:23

Scott

1,208
9
28

1

I appreciate. This is simply superb. Btw in my situation the above accepted answer did not work. But Your answer did the trick... Thanks. – Denn Sep 16 '16 at 02:17
1

Neat trick! If people are planning to use this, check the documentation on PARSENAME first and it'll only work for strings with 4 parts or less. – Mr Moose Dec 16 '16 at 05:49

score 7 · Accepted Answer · answered May 11 '15 at 16:18

7

One way:

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

answered May 11 '15 at 16:18

Alex K.

171,639
30
264
288

Nice! Worked perfectly. Ty. – DanielG Apr 27 '18 at 02:38

score 0 · Answer 3 · answered Nov 03 '16 at 15:58

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

SQL select to get a string between two spaces

3 Answers3

Linked