Comparing varchar and char in where statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing varchar and char in where statement

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2002-05-08 : 09:20:05

I have the following query :
select * from rates, RATES_STABLE_CURRENCIES_V
where rates.currency =convert(RATES_STABLE_CURRENCIES_V.ccy as char(3))

Rates.currency is a char(3), RATES_STABLE_CURRENCIES_V.ccy is a varchar(200)

When I run it, I have the following error
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Mathias
Posting Yak Master

119 Posts

Posted - 2002-05-08 : 09:35:27

solved :
select RATES.* from RATES, RATES_STABLE_CURRENCIES_V
where RATES.CURRENCY=
cast(RATES_STABLE_CURRENCIES_V.CCY as char(3)) COLLATE French_CS_AS

I don't know what the collate does, but this is working

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-12 : 19:30:21

In SQL 2000 collation can be defined down to the column level
In doing character comparison collation is obviously important and if your table column has been defined as a different collation than your default db collation you need to add the collate clause so that SQL is able to compare the 2 character values

Check out Collation Precedence in Books on Line

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources