10

I have the necessity to extract content from a text in a SQL field after a keyword. For example if i have a field called Description in a table, and the table 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 simply show it with a select.

ΩmegaMan
  • 26,526
  • 10
  • 91
  • 107
zim90
  • 103
  • 1
  • 1
  • 4
  • Might be relevant: http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/ – spenibus Aug 06 '15 at 10:43
  • possible duplicate of [How do I use regex in a SQLite query?](http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query) – Panagiotis Kanavos Aug 06 '15 at 11:19

2 Answers2

23

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.

GEOCHET
  • 20,745
  • 15
  • 72
  • 98
psoshmo
  • 1,383
  • 8
  • 19
  • out of curiosity, what was the reason for the downvote? did I misunderstand the question, or is my answer not sufficient? – psoshmo Aug 06 '15 at 13:59
  • that wasn't me to downvote. However i think this is the correct answer to my question – zim90 Aug 06 '15 at 14:11
  • @zim90 no worries, glad it helped – psoshmo Aug 06 '15 at 14:22
  • This seems to be the most elegant solution on the net. I have seen several others on TechNet forums that were marked as answers that didn't work or were 5 or more lines. – montag Aug 31 '17 at 14:38
  • SELECT SUBSTRING(YourField, INSTR(Keyword,YourField) + LENGTH(Keyword), LENGTH(YourField)) for the mysql folks out there. – Alex Oct 21 '21 at 21:57
  • This solution gets all characters/words after the keyword. So if there are words after the `Dog` or `Cat`, that will be gotten to...not just the following word. – ΩmegaMan May 02 '22 at 17:52
-1
SELECT SUBSTRING(ColumnName, CHARINDEX('Keyword', ColumnName), LEN(ColumnName)) FROM TableName
rchacko
  • 1,839
  • 21
  • 23