15

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.

user4157124
  • 2,809
  • 13
  • 27
  • 42
zim90
  • 153
  • 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

3 Answers3

30

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
  • 21,119
  • 15
  • 74
  • 98
psoshmo
  • 1,490
  • 10
  • 19
  • 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
  • can you please provide the query equivalent in oracle SQL? – Ra-V Jun 30 '23 at 07:46
  • Equivalent for this in Oracle SQL please? – Ra-V Jun 30 '23 at 07:51
0

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))
Baba Fooka
  • 29
  • 4
-1
SELECT SUBSTRING(ColumnName, CHARINDEX('Keyword', ColumnName), LEN(ColumnName)) FROM TableName
rchacko
  • 1,965
  • 23
  • 24