T-SQL with like sensitive to case

Question

T-SQL with like sensitive to case

Navigation

#1 by (5 votes)
#2 by (0 votes)

4

Is it possible to query in SQL Server 2008 with like sensitive? Actually, I'm looking at a column with a list of acronyms, which represent system flags, which are case sensitive.

The question is not about the performance of this search, since there will always be another indexed field being filtered in the same query.

sql-server tsql operador-like

asked by anonymous 30.01.2014 / 13:46

2 answers

0

I tested it here and it worked (my bank is SQL_Latin1_General_CP1_CI_AS = Insensitive)

The existing data: Insert into names (name) Select 'Rogério S. Ferreira'

Search options:

Select * from names where name like 'Rog%'
Select * from names where name like 'ROG%'
Select * from names where name like 'rog%'

By being "insensitive" all searches work ... But if you add this: "Collate SQL_Latin1_General_CP1_CS_AS" (Sensitive), only the first one will work, like this:

Select * from names where name like 'Rog%' Collate SQL_Latin1_General_CP1_CS_AS
Select * from names where name like 'ROG%' Collate SQL_Latin1_General_CP1_CS_AS
Select * from names where name like 'rog%' Collate SQL_Latin1_General_CP1_CS_AS

30.01.2014 / 15:27

mPDF - Error generating PDF | Message: preg_replace (): How to add a class to multiple cached elements at once?

score 5 · Accepted Answer

Yes it is possible, but first you need to know what type of COLLATION your SQL SERVER is configured for. To do this check with the following Select:

select databasepropertyex('databasename', 'collation') sqlcollation;

If you have a result like:

sql_latin1_general_cp1_ci_as

It means that Bank is not case sensitive and differs accents;

You can:

Change the default SQL Server collation to a new
Change the database grouping, but do not advise, as it is not easy, you will probably have to migrate from an old database to a new one
Create columns using a non-standard collation Change collation directly (same syntax is used to create columns with different colums)
```
select coluna1 collate sql_latin1_general_cp1_ci_as as coluna1 from tabela1
```

I think it's the best way forward, unless your database is really big and very, very confusing, is to create a new database with the correct collation.

There are a few ways to do this but I prefer my old database script and use this script to create a new one with the right collation, then migrate all the information by selecting the old database and inserting into the new one using the clause grouping for the new grouping in the varchar columns (avoiding the invalid grouping error).

The rationale is simple: changing a single column grouping for string comparison is very costly.