CODEWITHSUNDEEP
sqlsql-server
Gabrielizalo
Gabrielizalo

Reputation: 946

How to search text in SQL Server ignoring special chars?

I need to search in a table that has a text field (named Description) with special characters: ñ, á, ó...

Ex: Description = "La canción tiene buen ritmo!!!"

I can search for 'cancion' or 'canción' and this row need to be returned.

So, I did:

  1. A function that replace this special chars.

  2. A select that use it to search in the table:

    SELECT IdItem, Description
  FROM tblItemsTable
 WHERE dbo.fnReplaceSpecialChars ( Description ) LIKE '% word%'
    OR dbo.fnReplaceSpecialChars ( Description ) LIKE 'word%'

My problem is this search is very slow...

Can it be done in a better/optimize way?

Thanks!

Upvotes: 1

Views: 453

Answers (1)

Martin Smith
Martin Smith

Reputation: 453990

You can use an accent insensitive collation (name containsAI) to avoid having to use your function

SELECT 1
WHERE 'cancion' LIKE '%canción%' COLLATE Latin1_General_100_CS_AI


SELECT 1
WHERE 'cancion' LIKE '%canción%' COLLATE Latin1_General_100_CS_AS

Both use of leading wild cards and an explicit collate clause mean that an index could not be used so probably won't help much if at all on the speed front. You may want to look into setting up a full text index for this.

You would need to build the Full Text catalogue WITH ACCENT_SENSITIVITY =OFF

Upvotes: 4

Related Questions