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
– Alex Weitzer, Commented May 11, 2015 at 22:59
@AlexWeitzer : Okay, there's no need to bring politics into this discussion - things are confusing enough as it is! :) — marky
– marky, Commented May 12, 2015 at 19:46

Alex K. · Accepted Answer · 2015-05-11 16:18:59Z

8

One way:

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

answered May 11, 2015 at 16:18

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DanielG Over a year ago

Nice! Worked perfectly. Ty.

AHiggins · Accepted Answer · 2015-05-11 17:18:12Z

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, 2015 at 17:18

AHiggins

7,2357 gold badges38 silver badges56 bronze badges

answered May 11, 2015 at 16:23

Scott

1,2289 silver badges29 bronze badges

2 Comments

Denn Over a year ago

I appreciate. This is simply superb. Btw in my situation the above accepted answer did not work. But Your answer did the trick... Thanks.

Mr Moose Over a year ago

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.

Pawel Czapski · Accepted Answer · 2016-11-03 15:58:46Z

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

Collectives™ on Stack Overflow

SQL select to get a string between two spaces

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related