Special Characters in SQL Server

Databases sometimes get unexpected characters in their data.

If your customer’s name is “José” but you search for “Jose”, you won’t (by default) find him.

Here’s a simple way to take care of that in your SQL database, without changing the data that you have.

select 'áàâãäåāăąćĉċčďđéëêèēĕėęĝğġģĥħìíîïĩīĭįıĵķĺļľłñńņňòóôõöōŏőơŕŗřśŝşšţťŧùúûüũūŭůűųưŵŷźżž'
select 'áàâãäåāăąćĉċčďđéëêèēĕėęĝğġģĥħìíîïĩīĭįıĵķĺļľłñńņňòóôõöōŏőơŕŗřśŝşšţťŧùúûüũūŭůűųưŵŷźżž' Collate SQL_Latin1_General_CP1253_CI_AI
select 'ĥéĺłô ŵòŕľđ' Collate SQL_Latin1_General_CP1253_CI_AI

Using Collate SQL_Latin1_General_CP1253_CI_AI, you can leave all the accents in your data, but still treat it like it’s all made up of “normal” characters.

It’s also great as you’re transferring data from one system to another, if the second system can’t handle those characters because of technology or programming limitations.

Advertisements