SQL Select part of string

Question

I have a string that looks a bit like the following:

189 A 190 Merit 191 68.6

Now I want the value that falls between 190 and 191 - Merit.

Is this possible?

Do you always search for 190 and 191 or is there any other rule? — Peter Lang
– Peter Lang, Commented Oct 16, 2012 at 9:49
You need to provide more than one example, preferable exposing any "interesting" cases (different positions? no value?), if you want a decent answer. At the moment, SELECT 'merit' would be a valid query satisfying your requirements. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 16, 2012 at 9:50
I will always be searching for a value that is between 190 and 191 (it never changes). Such as 189 B 190 Pass 191 50.6 — nowYouSeeMe
– nowYouSeeMe, Commented Oct 16, 2012 at 9:53

RichardTheKiwi · Accepted Answer · 2012-10-16 10:00:40Z

2

Being naive - you say you have a string (i.e. not a column).

declare @astring nvarchar(max);
set @astring = '189 A 190 Merit 191 68.6';

The next 2 statements strip out the part between 190 and 191.

set @astring = stuff(@astring,1,patindex('%190%',@astring)+2,'');
set @astring = stuff(@astring,patindex('%191%',@astring+'191'),len(@astring),'');
set @astring = LTRIM(RTRIM(@astring));

select @astring;  -- 'Merit'

If you had meant a table column, then

declare @t table (astring nvarchar(max));
insert @t select
'189 A 190 Merit 191 68.6' union all select
'189 A 19 Merit 191 68.6 oops bad string' union all select
'' union all select -- make sure it doesn't crash on empty string
null union all select -- ditto null
'189 C 190 Pass 191 50.1';

select astring, s2=stuff(s1,patindex('%191%',s1+'191'),len(s1),'')
from
(
select astring, s1=stuff(astring,1,patindex('%190%',astring+'190')+2,'')
from @t
) x

-- result
ASTRING                                      S2
189 A 190 Merit 191 68.6                     Merit
189 A 19 Merit 191 68.6 oops bad string      (null)
                                             (null)
(null)                                       (null)
189 C 190 Pass 191 50.1                      Pass

edited Oct 16, 2012 at 10:00

answered Oct 16, 2012 at 9:54

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

3 Comments

nowYouSeeMe Over a year ago

Just out of interest where would I add an inner join to my Query?

RichardTheKiwi Over a year ago

In the inside where @t is. @t itself should be your table name. Instead of returning all columns, add 1 more, which is the s1 manipulation. Subquery it, and perform the s2 manipulation again.

nowYouSeeMe Over a year ago

Out of interest if I wanted to get just 68.6 can I take part of the string from 191 onwards?

Collectives™ on Stack Overflow

SQL Select part of string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related