How to Use Like with Column Name

how to use LIKE with column name

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, '%')

How to use column name as part of a LIKE statement in a WHERE clause of a JOIN

I am not sure if I completely understand the question but it appears you want to apply a predicate to a like statement that compares something else that is fixed and not in text you supply.

I always do something like this:

Where (ColumnName) like '%' + (OtherColumnName) + '%'

This is basically applying a wildcard around either side of the column you are trying to relate to. It will work for variables or columns.

UPDATE 5-21-13

Simple example of two table variables that self populate and work with wild card matcing on the second table variable. Code will run as is on SQL 2008 or higher in SQL Management Studio:

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Sean'),('Chad'),('Michael'),('Ray'),('Erik'),('Queyn');

declare @Match table ( Description varchar(4));

insert into @Match values ('B'), ('S'),('e')

Select top 100
    p.*
,   m.Description as 'MatchedOn'
from @Person p, @Match m
where p.person like '%' + m.Description + '%'

How to combine a column name into MySQL LIKE clause

Try this query, it should work.

SELECT * FROM t_cities as a  
JOIN temp_table as b  
ON a.NAME   LIKE concat("%",b.token);

Note- This query will not going to be fast as normal join and will take time.

sql select with column name like

You cannot with standard SQL. Column names are not treated like data in SQL.

If you use a SQL engine that has, say, meta-data tables storing column names, types, etc. you may select on that table instead.

LIKE with % on column names

Try this

SELECT * 
FROM account_invoice,sale_order
WHERE sale_order.name LIKE '%'  || account_invoice.origin || '%'

% needs single quote because the pattern is a string.

|| is the operator for concatenation.

How to query in sqlite using like keyword with column name?

The string concatenation operator in SQLite is ||. And you can express this as a JOIN:

SELECT A1
FROM A JOIN
     B 
     ON A.A2 LIKE '%' || B.B2 || '%' 
WHERE B.B1 = 1;

Note that || is the SQL standard string concatenation operator. + is overloaded in just a handful of databases owned by Microsoft (mostly).

Applying LIKE function on column

'%sb.column_2%' searches for the string 'sb.column_2' within asa.column_1.
You probably need

ON asa.column_1 LIKE '%' || sb.column_2 || '%'

You could also use

ON POSITION(sb.column_2 IN asa.column_1) > 0

But both will always result in a product join which will consume a lot of CPU unless at least one table has a low number of rows. Your DBA will definitely not like that :-)

This join probably indicates a bad datamodel, it should be fixed first thus avoiding bad joins like this.

How to deal with SQL column names that look like SQL keywords?

Wrap the column name in brackets like so, from becomes [from].

select [from] from table;

It is also possible to use the following (useful when querying multiple tables):

select table.[from] from table;

---

Related Topics