how can write SQL LIKE statement

[Guest_imported]

I want to write a query as folows:

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

But there is no result , may be because % not work

please help me

 

[sgursahaney]

You could use the CHARINDEX function to accomplish the same query. Try:

SELECT * FROM table1,table2 WHERE CHARINDEX(table2.f1, table1.f1) > 0

However, in order for this to work, your column types would need to be VARCHAR rather than CHAR. SQL Server pads values in CHAR columns with blanks to the maximum size of the column.

Regards,
Suresh

 

[tlbroadbent]

How are your columns defined - what data types? You may need to trim the contents of table2.f1.

SELECT * FROM table1,table2
WHERE table1.f1 LIKE '%' + RTRIM(table2.f1) + '%'

RTRIM will trim trailing spaces. Use LTRIM to trim leading spaces.

NOTE: It is always good practice to name the columns in a SELECT query when performing a JOIN. You may encounter problems if the tables have duplicate column names. You eliminate the duplicate names by using ALIASES.

SELECT table1.f1, table1.f2, table1.f3, table2.f1 As f1_2, table2.f2 As f2_2, ...
FROM table1,table2
WHERE table1.f1 LIKE '%' + rtrim(table2.f1) + '%'
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin