71

Normally LIKE statement is used to check the pattern like data.

example: 

select * from table1 where name like 'ar%'

My problem is to use one column of table with LIKE statement.

example:

select * from table1, table2 where table1.x is like table2.y%

Query above results error . how to use one column data in like query?

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
ArK
  • 20,698
  • 67
  • 109
  • 136

8 Answers8

99

You're close.

The LIKE operator works with strings (CHAR, NVARCHAR, etc). so you need to concattenate the '%' symbol to the string...


MS SQL Server:

SELECT * FROM table1,table2 WHERE table1.x LIKE table2.y + '%'


Use of LIKE, however, is often slower than other operations. It's useful, powerful, flexible, but has performance considerations. I'll leave those for another topic though :)


EDIT:

I don't use MySQL, but this may work...

SELECT * FROM table1,table2 WHERE table1.x LIKE CONCAT(table2.y, '%')
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • i have error as follows ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ %' at line 1 – ArK Sep 09 '09 at 10:19
  • 1
    MySQL doesn't use + as a concatenation operator. Apparently it's a function called concat() with different functions existing for different data types... – MatBailie Sep 09 '09 at 10:42
  • 4
    Apreciate you started your answer with "you're close" unlike most arrogant people here who like to discourage pple. tx – Mehdi Nov 09 '17 at 21:37
  • @MatBailie is this possible with PostgreSQL in any way at all - the above syntax does not work apparently. – Pravin May 31 '19 at 09:50
32

SQL SERVER

 WHERE ColumnName LIKE '%'+ColumnName+'%'
Yi Jiang
  • 49,435
  • 16
  • 136
  • 136
RoRo
  • 321
  • 3
  • 2
  • 10
    Thank you for also answering this in SQL SERVER. Despite @caitriona's comments, people are actually using other DB's out there and may have found this post by using Google – whiteshooz Oct 21 '15 at 21:32
9

ORACLE DATABASE example:

select * 
from table1 t1, table2 t2
WHERE t1.a like ('%' || t2.b || '%')
Stephen Docy
  • 4,738
  • 7
  • 18
  • 31
alvaroIdu
  • 443
  • 4
  • 7
7
...
WHERE table1.x LIKE table2.y + '%'
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • Maybe you need brackets? WHERE table1.x LIKE (table2.y + '%') -- or maybe "||" instead of "+" as per the SQL standard. – Adrian Pronk Sep 09 '09 at 10:21
  • 1
    I tried this but it didn't work. By reading your answer I realized that the `table2.y + '%'` part had to be on the right side of the `LIKE`. – GuiRitter Jan 25 '23 at 15:54
4
declare @LkeVal as Varchar(100)
declare @LkeSelect Varchar(100)

Set @LkeSelect = (select top 1 <column> from <table> where <column> = 'value')
Set @LkeVal = '%' + @LkeSelect

select * from <table2> where <column2> like(''+@LkeVal+'');
Koekiebox
  • 5,793
  • 14
  • 53
  • 88
0

For SQLLite you will need to concat the strings

select * from list1 l, list2 ll 
WHERE l.name like "%"||ll.alias||"%";
Bhaskar
  • 1,838
  • 1
  • 16
  • 29
0

for MySql you use like below,which is worked for me

SELECT * FROM table1, table2 WHERE table1.x LIKE (table2.y);

It takes table2's column values of y.

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
S Nagendra
  • 33
  • 4
0

This works for me:

SELECT * FROM `mdl_user` WHERE `firstname` LIKE concat(lastname,'%') AND lastname != ''
Elikill58
  • 4,050
  • 24
  • 23
  • 45
M Deril
  • 1
  • 1