SQL Server Select values from a table where a text field does not contain a word present in another column

eyh26e7m 于 11个月前发布在其他

关注(0)|答案(2)|浏览(64)

I have a table called COMMENTS that looks like this:
| First Name | Text |
| ------------ | ------------ |
| Frankie | Frankie is... |
| Sarah | Sarah is... |
| Graham | Graham is... |

and another table, NAMES, that lists all the names:

First name
Frankie
Sarah
Graham
Richard
Graham

I am trying to write a SELECT statement that returns all the rows where the text in 'Text' contains a name from 'First name', which isn't their own.

The issue I have faced so far is that the 'First Name' always returns true because their own name exists in the list of all names.

I'm after a statement that returns when:

If 'Text' contains a name from NAMES that isn't their own return that row.

Is this even possible with SELECT statements?

sql-server

来源：https://stackoverflow.com/questions/77728461/select-values-from-a-table-where-a-text-field-does-not-contain-a-word-present-in

2条答案

按热度按时间

vatpfxk51#

This returns comments with all the names in their text, except the commenter's own name:

select  *
from    COMMENTS c
join    NAMES n
on      n.FirstName <> c.FirstName
        and c.Text like '%' + n.FirstName '%'

赞(0）回复(0）举报 11个月前

5fjcxozz2#

Not obvious, but doable:

WITH
-- in data - not part of final query ..
comments(First_Name,Text) AS (
          SELECT 'Frankie','Frankie is tall'
UNION ALL SELECT 'Sarah','Sarah is Richard''s friend'
UNION ALL SELECT 'Graham','Graham is taller than Frankie'
)
,
fname(First_name) AS (
          SELECT 'Frankie'
UNION ALL SELECT 'Sarah'
UNION ALL SELECT 'Graham'
UNION ALL SELECT 'Richard'
UNION ALL SELECT 'Graham'
)
-- end of input, real query starts here
-- replace following comma wiht "WITH" ..
SELECT
  comments.*
, fname.first_name as ffname
FROM fname
JOIN comments 
  ON comments.first_name <> fname.first_name
 AND comments.text LIKE '%'||fname.first_name||'%'
;
-- out  First_Name |             Text              | ffname  
-- out ------------+-------------------------------+---------
-- out  Sarah      | Sarah is Richard's friend     | Richard
-- out  Graham     | Graham is taller than Frankie | Frankie

赞(0）回复(0）举报 11个月前

我来回答

SQL Server Select values from a table where a text field does not contain a word present in another column

2条答案

相关问题

热门标签

最新问答