是否可以从SQL查询中获取匹配的字符串？

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.

如果我有一个查询返回数据库中所有匹配条目,在可搜索列中有“新闻”(即SELECT * FROM表WHERE列LIKE%news%),并且一个特定行的条目以“In recent World news”开头,索马里被......入侵,我可以返回SQL条目的特定“块”吗?有点像预告片,如果你愿意的话。

4 个解决方案

#1

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.

基本上,在“新闻”这个词之前的10个字符的字符串子串,并且持续20个字符。

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

编辑:您需要确保“新闻”不在前10个字符中并相应地调整起始位置。

#2

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

您可以在SELECT部分中使用子字符串函数。就像是:

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

This will return the first 20 characters from column column

这将从列列返回前20个字符

#3

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.

我遇到了同样的问题,我最终将整个字段加载到C#中,然后重新搜索文本中的搜索字符串,然后选择x字符。

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

这适用于LIKE,但不适用于使用FORMS OF INFLECTION的全文查询,因为当您搜索“女性”时,这可能与“女性”匹配。

#4

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

如果您使用的是MSSQL,则可以执行各种类似VB的子字符串函数作为查询的一部分。

#1

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