Problem on comparing strings

Hi All,

I've problem on comparing strings with the following SQL statement:

select * from events where venue = 'myhome'

where venue is of type varchar(50)

The above SQL should return something (i.e. I've 3 events hold at my home!) but it return 0 rows.

Please kindly help.

Thanks,

stard

select * from events where venue LIKE '%myhome%'

|||

may be your column valeus have carrage return (ascii 13+10 = \r\n) character..When you see the result in GRID VIEW on Query Analyzer you wont find this character..

You can apply the following statement on select..

select * From Events Where replace(Venue,char(13)+Char(10),'')= 'MyHome'

or

select * from events where venue LIKE '%myhome%' -- It may return unexpected additional values

To remove this invalid character from your table

Update Events Set Venue = replace(Venue,char(13)+Char(10),'') Where Venue Like '%' + char(13)+Char(10) + '%'

|||

There are several possible explanitions. One is Collation differences.

What happens if you revise your query to this:

SELECT * FROM Events WHERE upper(Venue) = upper('myhome')

(This will not use indexing so may take some time, but at least you will find out if the collation is the issue.)

|||

Hi Nitin, ManiD & Arnie,

Thanks for advise abd i found a space character at the end of the strings.

I think i better trim before kicking them into the database :)

Cheers,

Stard

|||

I don't think that shouldn't be a problem if you are doing equality in most situations:

select case when 'fred' = 'fred ' then 'Yes' Else 'No' end

Will likely return Yes. What is the collation of your database?

select databasepropertyex(db_name(),'Collation')