clarification

[Hema Bairavan]

hi peoples, select * from table where lower(column) like lower('%test%') this statement works fine for me, but declare @abc char(50) set @abc='%test%' select * from table where lower(column) like lower(@abc) its not working for me? should i go for dynamic query though?? can any one help me? thanks in advance and happy new year

[Abhijit Jana]

Hema Bairavan wrote:

its not working for me?

What error you are getting ?

Abhijit Jana | Codeproject MVP Web Site : abhijitjana.net Don't forget to click "Good Answer" on the post(s) that helped you.

[Andy_L_J]

Try this (I haven't tested)

DECLARE @ABC VARCHAR(50)
DECLARE @SQL VARCHAR(1000)
SET @ABC = '%test%'
SET @SQL = 'SELECT * FROM table WHERE lower(column) LIKE (''' + @ABC + ''')'
EXEC (@SQL)

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Hema Bairavan]

abhijit, i am not getting any error, but the result s not correct, its not filtering the correct data from the database.and sorry for late reply.

[Hema Bairavan]

thanks for response andy, regarding dynamic query i can do, but it will affect the perfomance, because it has to compile when ever you are running the select statement(as i m using the same in procedure) my question is y cant a static one?

[DarrenShultz]

Haven't tried this, but noticed you are using the type char(50) instead of varchar(50). Is the char(50) adding padded characters to the end of the string, causing it to not match?

[Hema Bairavan]

yes darren, thanks for your info. me to not noticed the same. its working fine when i execute from the procedure from the SQL Query editor. but if i run from the application (asp.net C#) using command.executereader(), i m not getting the result, do you have any ideea?