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