CODEWITHSUNDEEP
sql
Anders
Anders

Reputation: 12560

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

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.

Upvotes: 2

Views: 582

Answers (4)

Bob Probst
Bob Probst

Reputation: 9641

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.

Upvotes: 7

Andrew Bullock
Andrew Bullock

Reputation: 37378

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".

Upvotes: 1

Diodeus - James MacFarlane
Diodeus - James MacFarlane

Reputation: 114377

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

Upvotes: 0

Rockcoder
Rockcoder

Reputation: 8479

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

Upvotes: 2

Related Questions