Unable to do LIKE search on word containing "aa"

views:

103

answers:

+1 Q:

Unable to do LIKE search on word containing "aa"

I have a field with the following content in a record in my SQL Server database:

salsaaften

It means "salsa evening" in danish for those interested :)

When I do like this I am not able to find the record:

SELECT * FROM table_name WHERE field_name LIKE '%salsa%'

However, when I do this, I find the record perfectly fine:

SELECT * FROM table_name WHERE field_name LIKE '%sals%'

The same thing happens with "aften" and "ften". No results when searched for "aften", but a perfect match when searched for "ften".

It looks like it's the "aa" that are confusing the database.. But why? And how to fix?

Thanks in advance :)

+4 A:

The sequence "aa" in some collations is treated as a single character. You might want to check to make sure the collation is the one you want.

Mark Wilkins 2010-01-05 13:23:00

And which one do I want? :S

EmKay 2010-01-05 13:31:14

If aa is treated as a single Danish character, then you probably are using the correct one. If you really want to treat it as separate "a" characters in this case, you can probably use a collation cast (http://msdn.microsoft.com/en-us/library/ms184391.aspx) in the query.

Mark Wilkins 2010-01-05 13:47:57

Like this?WHERE field_name LIKE '%salsa%' COLLATE Danish_Norwegian_CI_AS?Doesn't work.Have also tried WHERE field_name COLLATE Danish_Norwegian_CI_AS LIKE '%salsa%'and in both cases I have tried both CI_AS, CI_AI, CS_AI, CS_ASNothing works :(

EmKay 2010-01-05 13:58:13

I'm at home right now and can't test it. However, you need to use a collation that does not treat the aa as a single character. I think you can use SQL_Latin1_General_Cp1_CI_AS.

Mark Wilkins 2010-01-05 14:31:32

But does this collation still allow the use of special characters like æ, ø and å? What are the differences excactly? Does a list exist with a comparison of the different collations?

EmKay 2010-01-05 14:34:47

You should be able to use those characters and match them with that collation in a LIKE clause. However it will not normalize, for example ae to æ. The sorting would be different, however, sort order is not important for the LIKE operator. Google should quickly turn up a site with the collation specifics and differences. I don't have one memorized, but I have seen it.

Mark Wilkins 2010-01-05 14:41:46

I found out that I could change the collation for only that field. Now it works and it also finds for example "klæder" if I do a search on "ae".Thank you very much!

EmKay 2010-01-06 12:59:57

ansaurus

tags:

views:

answers:

Unable to do LIKE search on word containing "aa"

related questions