CODEWITHSUNDEEP
sqlsqlite
zim90
zim90

Reputation: 173

Extract string from a text after a keyword

I want to extract content from text in an SQL field after a keyword. I have a field called Description in a table, the content for that field is:

asdasf keyword dog

aeee keyword cat

ffffaa keyword wolf

I want to extract and save the text after "keyword " (in this case dog,cat and wolf) and save it in a view or show it with select.

Upvotes: 17

Views: 83636

Answers (3)

Baba Fooka
Baba Fooka

Reputation: 39

Just to add to @psoshmo's answer If keyword is not found it will substring the original string, to counter this I have added a case like below

SUBSTRING(YourField, CHARINDEX('Keyword',YourField) + (CASE WHEN ', ' + YourField +',' LIKE '%Keyword%' THEN LEN('Keyword') ELSE 1 END), LEN(YourField))

Upvotes: 1

rchacko
rchacko

Reputation: 2119

SELECT SUBSTRING(ColumnName, CHARINDEX('Keyword', ColumnName), LEN(ColumnName)) FROM TableName

Upvotes: -2

psoshmo
psoshmo

Reputation: 1550

Here is an example using SUBSTRING():

SELECT SUBSTRING(YourField, CHARINDEX(Keyword,YourField) + LEN(Keyword), LEN(YourField))

Another example:

declare @YourField varchar(200) = 'Mary had a little lamb'
declare @Keyword varchar(200) = 'had'
select SUBSTRING(@YourField,charindex(@Keyword,@YourField) + LEN(@Keyword), LEN(@YourField) )

Result:

 a little lamb

Please note that there is a space before the 'a' in this string.

Upvotes: 35

Related Questions