SQL Query: returning if column contains substring

Question

I have two tables tableA and tableB.

I want to write a query where it displays name and id if the string value of tableA's id is a substring of tableB's name. I tried the like operator but can't seem to get it to work.

   tableA
id   |
bob
cat
sky
   tableB
name   |
bobby
catlyn
bret

answer table
id     |name   | 
bob    bobby
cat    catlyn

also this is not a proper way to handle data. you should have keys to bind the table. — T. Peter
– T. Peter, Commented Nov 24, 2020 at 9:45
What is exactly "doesn't work"? Your results are not the same as you've expected? So provide your query and it's output. If you have an error, then add the error message as plain text. — astentx
– astentx, Commented Nov 24, 2020 at 9:51
This was just an example data. I tried something like this "where tableB like %tableA.id%". But this doesn't work as it is not a string value and using + to concatenate doesn't work. — O.isik
– O.isik, Commented Nov 24, 2020 at 9:55

GMB · Accepted Answer · 2020-11-24 09:45:52Z

1

You can join on a like condition:

select a.id, b.name
from tablea a
inner join tableb b on b.name like '%' || a.id || '%'

You did not tell which database you are running, so this uses standard string concatenation operator ||; some databases use something else (eg: MySQL and SQLServer have function concat()).

answered Nov 24, 2020 at 9:45

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

SQL Query: returning if column contains substring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related