SQL ending in multiple values

Question

Below line from this page shows how to match based upon end characters.

select * from table where Name like '%es'

How could I modify the code if I want to match with multiple values. For example I have another table T2 that has column C1 and I want all values from column Name that end in values from column C1

if I want to find all Names that are in C1, I would use

 select * from table where Name in (select C1 from T2)

on a similar lines I want something like below. It is a pseudo code

  select * from table where Name like (select '%C1' from T2)

Column C1 and Name have thousands of values in them

Possible duplicate of How to use LIKE statement with multiple values from another field? — Liora Haydont
– Liora Haydont, Commented Aug 29, 2018 at 20:59
Which DBMS are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ... — user330315
– user330315, Commented Aug 31, 2018 at 19:05

D-Shih · Accepted Answer · 2018-08-29 21:07:58Z

2

You can try to use exists and subquery makes LIKE column value by + or || depend on your DBMS.

select * 
from table t1
where exists(
   SELECT 1 
   FROM T2 
   WHERE t1.Name LIKE '%' + T2.C1
)

or

select * 
from table t1
where exists(
   SELECT 1 
   FROM T2 
   WHERE t1.Name LIKE '%' || T2.C1
)

Here is a mysql sample.

CREATE TABLE T1(
   Name varchar(50)
);

INSERT INTO T1 VALUES ('cat hat');
INSERT INTO T1 VALUES ('cat aaa');

CREATE TABLE T2(
   C1 varchar(50)
);

INSERT INTO T2 VALUES ('cat');

Query 1:

select * 
from  t1
where exists(
   SELECT C1 
   FROM t2
   WHERE t1.Name LIKE '%' || T2.C1
)

Results:

|    Name |
|---------|
| cat hat |
| cat aaa |

edited Aug 29, 2018 at 21:07

answered Aug 29, 2018 at 21:00

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

2 Comments

Ni_Tempe Over a year ago

thanks. What is the purpose of SELECT 1 FROM T2 ?

D-Shih Over a year ago

The EXISTS operator is used to test for the existence of any record in a subquery. select 1 mean get the value from the condition, you can also use SELECT * FROM T2 depend on you

ahuemmer · Accepted Answer · 2023-09-01 03:09:24Z

0

Let's say, we need to get column1, column2 from tablename, in which column2 ends with either A or B or C:

Then we can simply write below query:`

SELECT column1, column2, ...
FROM tablename
WHERE RIGHT(column2,1) in ('A','B','C');

edited Sep 1, 2023 at 3:09

ahuemmer

2,11717 gold badges30 silver badges38 bronze badges

answered Aug 30, 2023 at 4:00

Niranjan Reddy Vangala

1

Collectives™ on Stack Overflow

SQL ending in multiple values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related