Menu
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. Thank you.
260
CHARINDEX function in SQL queriesThe CHARINDEX() function returns the substring position inside the specified string. It works reverse to the SUBSTRING function. The substring() returns the string from the starting position however the CHARINDEX returns the substring position.
The SUBSTRING() function extracts some characters from a string.
You can use the MySQL SUBSTRING_INDEX() function to return everything before or after a certain character (or characters) in a string. This function allows you to specify the delimiter to use, and you can specify which one (in the event that there's more than one in the string).
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.
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
