Find a string between two string values

Question

Probably a simple one but I'm having trouble getting this to work in SQL. ( easy with VBA ).

Given xxx_123_abcd I need to extract the 123 part. whatever code searchs for the _ should always work from the left because sometimes and string might be xxx_123_abcd_xxx.

Thanks in advance for any advice :)

This is not very well explained. Can you elaborate on exactly what you need? — Sachin Kainth
– Sachin Kainth, Commented Jun 17, 2014 at 8:05
My advice is: Avoid strings like 'xxx_123_abcd_xxx' in an rdbms, if you are interested in the substrings. Have four columns instead: 'xxx', '123', 'abcd', 'xxx'. As to string manipulation: What do you want to extract? The first integer? The second substring? — Thorsten Kettner
– Thorsten Kettner, Commented Jun 17, 2014 at 8:10
possible duplicate of How to get a substring '403162' from the given string 'Praveen(403162)' in sql? — Tanner
– Tanner, Commented Jun 17, 2014 at 8:33

Dan · Accepted Answer · 2014-06-17 08:14:43Z

1

Use CHARINDEX to search for the underscores, to be able to extract the part of the string you need with SUBSTRING. Something like this should work:

SELECT
    SUBSTRING(MyColumn, CHARINDEX('_', MyColumn)+1,
        CHARINDEX('_', MyColumn, CHARINDEX('_', MyColumn)+1)-CHARINDEX('_', MyColumn)-1)
FROM
    MyTable

MyColumn = xxx_123_abcd_xxx produces 123 using the above code.

Assumptions: The string you are looking for is enclosed by the first and second occurence of an underscore.

answered Jun 17, 2014 at 8:14

Dan

10.8k27 silver badges51 bronze badges

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

1 Comment

Na'te Over a year ago

Thank you for the answer. To all, i should have specified that the return value i was looking for could be two didgits

Raj · Accepted Answer · 2014-06-17 08:15:30Z

1

Assumptions

The numeric string you are searching for is always three digits
It is always preceded by an '_'
It immediately follows the first occurrence of '_' in the string

Then this will work

SELECT SUBSTRING('xxx_123_abcd',CHARINDEX('_','xxx_123_abcd')+1,3)

answered Jun 17, 2014 at 8:15

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Comments

mohan111 · Accepted Answer · 2014-06-17 08:58:07Z

1

you can also go with this

SELECT LEFT(Col1,PATINDEX('%[^0-9]%', Col1+'a')-1) from(
    SELECT SUBSTRING(Col1, PATINDEX('%[0-9]%', Col1), LEN(Col1)) As Result From table
)x

answered Jun 17, 2014 at 8:58

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Comments

one angry researcher · Accepted Answer · 2014-06-17 09:22:41Z

1

Use a custom Split function such as described here.

If the xxx_123 part is always the same length, you could also use

SUBSTRING('xxx_123_abcd',5,7)

edited Jun 17, 2014 at 9:22

answered Jun 17, 2014 at 8:10

one angry researcher

6124 silver badges10 bronze badges

Collectives™ on Stack Overflow

Find a string between two string values

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related