Column Collation & using the Like operand
-
Hi guy's, here is my problem. I have a table that has a column that holds peoples names. This column has to accept text input in different languages. My problem starts when I try to retrieve the data.
The column is of data type nvarchar and I have set the column collation to be Modern_Spanish_CI_AI.
The following is an example of my problem
My column contains the name 'Muñoz' & also 'Munoz' (the first has the special character 'ñ')
If I run the following query
SELECT *
FROM MyTable
WHERE (MyColumn LIKE 'Muñoz')
I only get details where the text actually equals 'Muñoz', I want to return details where the text is like both 'Muñoz' & 'Munoz'. I thought that the collation settings took care of this issue?
The following query returns exactly what I want but I shouldn't have to use a wildcard should I?
SELECT *
FROM MyTable
WHERE (MyColumn LIKE 'Mu%oz')
Can anyone tell me what I am doing wrong?
-
Yes you would still need to use the wildcard. Or you might try using the regular expression engine using a range ( LIKE ('Mu[n,ñ]oz') Note that I don't have the ability to test this now as I'm at home and don't have SQL installed here). The reason is that the data is stored using the unicode value for the characters. Since they are different for the accented and non accented characters you don't find the match.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Thanks for your reply, I was hoping that there was some method whereby one character set cross-referenced another i.e. 'n' & 'ñ' would be interpretted as the same, wishful thinking on my part!
I think I will just code it so that users need to enter the wildcard for special characters. 
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply