Provide the values for a 'like' function from a specific column in a Table?

Question

I am using SQL Server 2014 and I need a T-SQL query which uses the like function to run on a specific column (c1) of a Table (t1) to find out if it contains one of the codes from a list of codes found in the column (c2) of another Table (t2).

To simplify, here is the scenario and the expected output:

Table t1:

ID       Notes
101      (free text in this column)
102      ...
...      ...
115000   ...

Table t2 (list of more than 300 codes):

Code
FR110419
GB150619
...
DE111219

What I am looking for:

SELECT ID 
FROM t1
WHERE t1.Notes like (SELECT Code FROM t2)

Since the like operator needs '%'to work, I am confused as to how to construct that line.

I have done some research on StackOverflow and the closest solution I have come across is for a mysql problem: how to use LIKE with column name

Any type of help will be most appreciated.

What should we do if the Code column has a null value? Return everything or return nothing? — Caius Jard, Oct 04 '19 at 06:04
The Code Column won't have a NULL value as it is a list of existing codes used. — user3115933, Oct 04 '19 at 06:11

score 3 · Accepted Answer · answered Oct 04 '19 at 05:23

You seem to be looking for a JOIN:

SELECT ID 
FROM t1
INNER JOIN t2 ON t1.Notes LIKE '%' + t2.Code + '%'

If different Codes might appear in the same Note, using an EXISTS condition with a correlated subquery is also an option, as it would avoid duplicating records in the output:

SELECT ID
FROM t1
WHERE EXISTS (
    SELECT 1 FROM t2 WHERE t1.Notes LIKE '%' + t2.Code + '%'
)

score 1 · Answer 2 · answered Oct 04 '19 at 05:36

You can use cross apply with charindex like this:

--Loading data
create table t1 (id varchar(10));
insert into t1 (id) values ('100100'),('200100'),('300100')
insert into t1 (id) values ('100200'),('200200'),('300200')
insert into t1 (id) values ('100300'),('200300'),('300300')
insert into t1 (id) values ('0100'),('0200'),('0300')
insert into t1 (id) values ('00010'),('00020'),('00030')

create table t2 (id varchar(10));
insert into t2 (id) values ('020'),('010')

select t.id
from t1 as t
cross apply t2 as t2
--where charindex(t2.id,t.id) > 0 -- simulates a double % one at the beginning and one at the end
--where charindex(t2.id,t.id) = 1 -- simulates a % at the beginning
where charindex(t2.id,t.id) = len(t.id)-len(t2.id)+1 -- simulates a % at the end

The only thing is that the table is very big this could be a slow solution.

This is real good. CHARINDEX will perform better than a LIKE statement. — Alan Burstein, Oct 04 '19 at 19:05

score 1 · Answer 3 · answered Oct 04 '19 at 19:24

Building on what's already been posted, you can create an indexed view to really speed things up.

Using CTE6's sample data...

--Loading data
create table t1 (id varchar(10));
insert into t1 (id) values ('100100'),('200100'),('300100')
insert into t1 (id) values ('100200'),('200200'),('300200')
insert into t1 (id) values ('100300'),('200300'),('300300')
insert into t1 (id) values ('0100'),('0200'),('0300')
insert into t1 (id) values ('00010'),('00020'),('00030')

create table t2 (id varchar(10));
insert into t2 (id) values ('020'),('010')
GO

--  The View
CREATE VIEW dbo.vw_t1t2 WITH SCHEMABINDING AS
SELECT     t1 = t1.id, t2 = t2.id, cb = COUNT_BIG(*)
FROM       dbo.t1 AS t1
CROSS JOIN dbo.t2 AS t2
WHERE      CHARINDEX(t2.id,t1.id) > 0
GROUP BY   t1.id, t2.id
GO
-- The index (may need to add something else to make UNIQUE)
CREATE UNIQUE CLUSTERED INDEX uq_cl_vwt1t2 ON dbo.vw_t1t2(t1,t2);
GO

This will perform very well for SELECT statements but could impact data modifications against t1 and t2 so make sure to use the smallest datatype possible and only include columns you are certain you need (Varchar(10) is good). I include COUNT_BIG() because it's required in indexed views that leverage GROUP BY.

Provide the values for a 'like' function from a specific column in a Table?

3 Answers3