'ß' = 'ss' - are they equal?

Hi,
I've set up an NVARCHAR table column having a UNIQUE constraint. This = UNIQUE constraint is supposed to keep double entries from being entered = into that column. So far so good...
In my new project I'm quite baffled about the fact that SQL Server 2000 = seems to believe that '=DF' and 'ss' are identical!
This assumption is only true for special cases. The German words = "Ma=DFe" and "Masse", for instance, have totally different meanings = ("measures", "mass"/"crowd").
How can I set up my table column definition (COLLATE perhaps?) in SQL = Server 2000 to have it regard '=DF' and 'ss' being distinct characters?
Your help is quite appreciated.
Best regards,
Axel DahmenAlex,
http://www.ureader.com/message/1343195.aspx has a conversation on this
subject you may find useful.
Most languages (especially English) have a fair number of homographs, which
are words spelled the same but with different pronunciations and meanings.
"Wind" can mean 'moving air' or 'tightening a spring', etc. I think that
you have probably run into this with your German words.
RLF
"Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
Hi,
I've set up an NVARCHAR table column having a UNIQUE constraint. This UNIQUE
constraint is supposed to keep double entries from being entered into that
column. So far so good...
In my new project I'm quite baffled about the fact that SQL Server 2000
seems to believe that 'ß' and 'ss' are identical!
This assumption is only true for special cases. The German words "Maße" and
"Masse", for instance, have totally different meanings ("measures",
"mass"/"crowd").
How can I set up my table column definition (COLLATE perhaps?) in SQL Server
2000 to have it regard 'ß' and 'ss' being distinct characters?
Your help is quite appreciated.
Best regards,
Axel Dahmen|||Great, Russel, thanks!
From the conversation you mentioned I've learned that according to DIN =and ANSI-SQL "=DF" equals to "ss". But in fact this isn't true anymore =since German has been updated to new spelling rules a few years ago.
With the spelling reform we've got they changed the meaning of "=DF". It =has now become a letter of its own and is no more a replacement for ="ss". They are even planning to introduce a new, "capital =DF" letter.
With the old spelling rules words like "Ma=DFe" were equal to "Masse". =But nowadays this isn't true anymore.
Are there any plans to update ANSI-SQL and SQL-Server collation rules =according to current German rules?
TIA,
Axel Dahmen
"Russell Fields" <russellfields@.nomail.com> schrieb im Newsbeitrag =news:e6VOWBxQIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Alex,
> > http://www.ureader.com/message/1343195.aspx has a conversation on this =
> subject you may find useful.
> > Most languages (especially English) have a fair number of homographs, =which > are words spelled the same but with different pronunciations and =meanings. > "Wind" can mean 'moving air' or 'tightening a spring', etc. I think =that > you have probably run into this with your German words.
> > RLF
> > > "Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message > news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> > I've set up an NVARCHAR table column having a UNIQUE constraint. This =UNIQUE > constraint is supposed to keep double entries from being entered into =that > column. So far so good...
> > In my new project I'm quite baffled about the fact that SQL Server =2000 > seems to believe that '=DF' and 'ss' are identical!
> > This assumption is only true for special cases. The German words ="Ma=DFe" and > "Masse", for instance, have totally different meanings ("measures", > "mass"/"crowd").
> > How can I set up my table column definition (COLLATE perhaps?) in SQL =Server > 2000 to have it regard '=DF' and 'ss' being distinct characters?
> > Your help is quite appreciated.
> > Best regards,
> Axel Dahmen
> > >=20|||Axel,
I imagine from the ANSI-SQL docuements that SQL is tracking with the ISO
standard on this. I see that the ISO collation standard was updated in
2007, but I am not willing to pay to find out what changed.
http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=44872
So, I don't know who to ask to get a good answer for you about what SQL is
doing. (Maybe Joe Celko has an update.)
In the meantime, if is worth the extra work for you, you can use this
workaround.
SELECT * FROM Composers
WHERE LastName = 'Strauß'
AND LastName COLLATE Latin1_General_BIN = 'Strauß'
The extra binary collation compare will ensure that you get 'Strauß' and do
not get ' Strauss'.
RLF
"Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
news:%23EJWJu4QIHA.5400@.TK2MSFTNGP04.phx.gbl...
Great, Russel, thanks!
From the conversation you mentioned I've learned that according to DIN and
ANSI-SQL "ß" equals to "ss". But in fact this isn't true anymore since
German has been updated to new spelling rules a few years ago.
With the spelling reform we've got they changed the meaning of "ß". It has
now become a letter of its own and is no more a replacement for "ss". They
are even planning to introduce a new, "capital ß" letter.
With the old spelling rules words like "Maße" were equal to "Masse". But
nowadays this isn't true anymore.
Are there any plans to update ANSI-SQL and SQL-Server collation rules
according to current German rules?
TIA,
Axel Dahmen
"Russell Fields" <russellfields@.nomail.com> schrieb im Newsbeitrag
news:e6VOWBxQIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Alex,
> http://www.ureader.com/message/1343195.aspx has a conversation on this
> subject you may find useful.
> Most languages (especially English) have a fair number of homographs,
> which
> are words spelled the same but with different pronunciations and meanings.
> "Wind" can mean 'moving air' or 'tightening a spring', etc. I think that
> you have probably run into this with your German words.
> RLF
>
> "Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
> news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I've set up an NVARCHAR table column having a UNIQUE constraint. This
> UNIQUE
> constraint is supposed to keep double entries from being entered into that
> column. So far so good...
> In my new project I'm quite baffled about the fact that SQL Server 2000
> seems to believe that 'ß' and 'ss' are identical!
> This assumption is only true for special cases. The German words "Maße"
> and
> "Masse", for instance, have totally different meanings ("measures",
> "mass"/"crowd").
> How can I set up my table column definition (COLLATE perhaps?) in SQL
> Server
> 2000 to have it regard 'ß' and 'ss' being distinct characters?
> Your help is quite appreciated.
> Best regards,
> Axel Dahmen
>
>

"Error converting data type nvarchar to real" using LIKE operator on CHAR column

View1 has a CHAR column of CharCol1. While using LIKE operator on this column, I receive below error.

Select * from View1 Where CharCol1 Like '%77%'

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to real.

But all of below syntax works:

Select * from View1 Where CharCol1 like N'%77%'

Select * from View1 Where CharCol1 Like '77'

Select * from View1 Where CharCol1 = '77'

Why does SQL 2005 treat '%77%' as real? This used to be working in SQL 2000.

It works for me in SQL 2005. Are you sure your view's CharCol1 is really a character column, and hasn't been casted as real?|||

Actually the view was being used in SQL 2000 and after SQL 2005 upgrade, this error has started.

Last view used a view and that view also uses another view. There are three levels. But one of the views used in the first view is then joined in the last view which gives this error. Of course, this is not good programming but it was working in the previous version. Now, I have removed this join and carried the necessary columns from the first level thus I eliminated double joining of same view at the last level. This solved the problem.

Actually, the problem is that, I had tested this view and they were ok. But once I give a Where clause using a column from the double joined view, I get this error. Unfortunately, we encountered this error after upgrade and on the live system we had to fix it.

What I understand is that somehow in SQL 2005, we can get errors if the coding is not good and the views should be tested by giving "Where" clauses using all possible columns.

Hope this is clear.

Does anyone have comment on this?