SQL Search rows that contain strings from 2nd table list

Question

I have a master table that contains a list of strings to search for. it returns TRUE/FALSE if any string in the cell contains text from the master lookup table. Currently I use excel's

=SUMPRODUCT(--ISNUMBER(SEARCH(masterTable,[@searchString])))>0

is there a way to do something like this in SQL? LEFT JOIN or OUTER APPLY would be simple solutions if the strings were equal; but they need be contains..

SELECT *
FROM t
WHERE col1 contains(lookupString,lookupColumn)


--that 2nd table could be maintained and referenced from multiple queries
hop
bell
PRS
2017

My desired results would be a column that shows TRUE/FALSE if the row contains any string from the lookup table

 SEARCH_STRING      Contained_in_lookup_column
 hopping                 TRUE
 root                    FALSE
 Job2017                 TRUE
 PRS_tool                TRUE
 hand                    FALSE

Edit your question and provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Nov 14, 2016 at 13:57

Sergio.Gaspar · Accepted Answer · 2016-11-14 14:47:56Z

1

Sorry i dont have access to the DB now to confirm the syntax, but should be something like this:

SELECT t.name,
case when (select count(1) from data_table where data_col like '%' || t.name || '%' > 0) then 'TRUE' else 'FALSE' end
FROM t;

or

SELECT t.name,
case when exists(select null from data_table where data_col like '%' || t.name || '%') then 'TRUE' else 'FALSE' end
FROM t;

Sérgio

answered Nov 14, 2016 at 14:47

Sergio.Gaspar

664 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Orin Moyer Over a year ago

I don't recognized the || ? maybe I should have posted the SQL version Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64) May 4 2015 18:48:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Sergio.Gaspar Over a year ago

Sorry, i assumed Oracle.. Try the ' + ' (plus) operator in Sql Server.. should do the trick

Orin Moyer Over a year ago

thank you kindly. that was so simple I feel dumb for asking

Orin Moyer Over a year ago

actually, I need to use CONTAINS instead of like.. is there a trick to make this work?

Sergio.Gaspar Over a year ago

Hi, sorry.. not an expert in SQL-SERV.. it has been a while but should be something like:

|

Frank Schmitt · Accepted Answer · 2016-11-14 14:01:59Z

0

You can use a combination of % wildcards with LIKE and EXISTS.

Example (using Oracle syntax) - we have a v_data table containing the data and a v_queries table containing the query terms:

with v_data (pk, value) as (
  select 1, 'The quick brown fox jumps over the lazy dog' from dual union all
  select 2, 'Yabba dabba doo' from dual union all
  select 3, 'forty-two' from dual
),
v_queries (text) as (
  select 'quick' from dual union all
  select 'forty' from dual
)
select * from v_data d
where exists (
  select null 
  from v_queries q
  where d.value like '%' || q.text || '%');

answered Nov 14, 2016 at 14:01

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Collectives™ on Stack Overflow

SQL Search rows that contain strings from 2nd table list

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related