CODEWITHSUNDEEP
sqlsql-serversubstring
Tim Boland
Tim Boland

Reputation: 10177

Selecting 1 word out of a field using SQL

I need to extract "SRN=123" (the 123 is dynamic in length, SRN= is consistent) from a column. Data could be anything, no consistent format

456 lorem limpsump SRN=123 and some more things 3.

I'm having trouble trying to use a charindex to find the ending point of SRN=123 to get the length, any help?

SUBSTRING(t.Instructions, 
                             CharIndex('SRN=', t.Instructions) + 10, 
                             (CHARINDEX('SRN=', t.Instructions )-(CharIndex('SRN=[^0-9.-]', t.Instructions) + 10)))

Upvotes: 3

Views: 227

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

select 'SRN='+left(stuff(@S, 1, charindex('SRN=', @S)+3, ''), patindex('%[^0-9]%', stuff(@S, 1, charindex('SRN=', @S)+3, '')+' ')-1)

https://data.stackexchange.com/stackoverflow/q/104003/

Upvotes: 1

Narnian
Narnian

Reputation: 3908

Sorry... missed that you were just wanting the SRN=12345

DECLARE @STRING VARCHAR(1000)
SELECT @STRING = '456 lorem limpsump SRN=123456 and some more things 3.'
SELECT SUBSTRING(@STRING, CHARINDEX('SRN=', @string, 0), CHARINDEX(' ', @string, CHARINDEX('SRN=', @string, 0)) - CHARINDEX('SRN=', @string, 0))

Upvotes: 0

josephj1989
josephj1989

Reputation: 9709

This works

with t as(
select '456 lorem limpsump SRN=12378 and some more things 3.' as col )

select substring(col,charindex('SRN=',col)+4,
charindex(' ',col,charindex('SRN=',col))-charindex('SRN=',col)-4)
from t

Upvotes: 0

paparush
paparush

Reputation: 1348

Making a GIANT assumption that there is a space at the end of the string you need.

declare @str as varchar(100)
set @str='456 lorem limpsump SRN=123 and some more things 3'

--for testing, find the starting point
select charindex('SRN=',@str)

--for testing, find the ending point
select charindex(' ',@str,charindex('SRN=',@str))

--find your substring 
select substring(@str,charindex('SRN=',@str),charindex(' ',@str,charindex('SRN=',@str))-charindex('SRN=',@str))

Upvotes: 1

Related Questions