Is it possible to get the matching string from an SQL query?

Question

If I have a query to return all matching entries in a DB that have "news" in the searchable column (i.e. SELECT * FROM table WHERE column LIKE %news%), and one particular row has an entry starting with "In recent World news, Somalia was invaded by ...", can I return a specific "chunk" of an SQL entry? Kind of like a teaser, if you will.

Answer 1

You can use substring function in a SELECT part. Something like:

SELECT SUBSTRING(column, 1,20) FROM table WHERE column LIKE %news%

This will return the first 20 characters from column column

Answer 2

If you are using MSSQL you can perform all kinds VB-like of substring functions as part of your query.

Answer 3

select substring(column,
                 CHARINDEX ('news',lower(column))-10,
                 20)
FROM table 
WHERE column LIKE %news%

basically substring the column starting 10 characters before where the word 'news' is and continuing for 20.

Edit: You'll need to make sure that 'news' isn't in the first 10 characters and adjust the start position accordingly.

Answer 4

I had the same problem, I ended up loading the whole field into C#, then re-searched the text for the search string, then selected x characters either side.

This will work fine for LIKE, but not full text queries which use FORMS OF INFLECTION because that may match "women" when you search for "woman".