If string is in field text then get another part of the string

Question

In SQL I have a field that has a lot of text in it.

I am trying to find a way to get text from the field if it contains certain words.

An example:

textField = 'This is the value for your spaceID=12345678'

textField = 'This is the value for your typeID=43254364'

So if the textField contains spaceID I want to return the value for it

If textField like spaceID then return 12345678

I am just not sure how to return the value of that ID.

Possible duplicate of SQL Server String extract based on pattern — Sanpas
– Sanpas, Commented Feb 28, 2019 at 15:56

Sanpas · Accepted Answer · 2019-02-28 15:56:23Z

1

Hi i think thoses example can help you :

declare @value varchar(400)

SET @value = 'This is the value for your spaceID=12345678'
select @value, CHARINDEX('=',@value,1), SUBSTRING(@value,CHARINDEX('=',@value,1) + 1 ,10)
where @value like '%spaceID%'

And see thoses different link :

answered Feb 28, 2019 at 15:56

Sanpas

1,18010 silver badges30 bronze badges

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

2 Comments

Pawel Czapski Over a year ago

This script fully rely on "=", it will not return expected result if some reason there is another occurrence of "=" .

Sanpas Over a year ago

@PawelCzapski thx you . Yes it’s just example they have to adapt this with they data

forpas · Accepted Answer · 2019-02-28 16:00:11Z

1

You can do it with substring():

select substring(textfield, charindex('spaceID=', textfield) + len('spaceID='), 100)
from tablename
where textfield like '%spaceID=%'

answered Feb 28, 2019 at 16:00

forpas

165k10 gold badges51 silver badges85 bronze badges

4 Comments

Pawel Czapski Over a year ago

this is more defensive answer, I like you used "len" instead hardcoding

forpas Over a year ago

i guess spaceID= is just a sample literal string, so the code should be flexible with any other string.

Pawel Czapski Over a year ago

Yeap, still better use "spaceID=" than just "=", as this may occur in other places also.

forpas Over a year ago

Just to be on the safe side.

Yogesh Sharma · Accepted Answer · 2019-02-28 16:01:33Z

1

You can use substring() :

select substring(textField, charindex('spaceID=', textfield) + 8, len(textField))

edited Feb 28, 2019 at 16:01

answered Feb 28, 2019 at 15:55

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

If string is in field text then get another part of the string

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related