T-SQL with case sensitive like

Asked

Viewed 2,443 times

4

Is it possible to make a query in SQL Server 2008 with sensitive like? Actually, I’m looking in a column with a list of acronyms, which represent system flags, which differentiate uppercase and lowercase.

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

sql-server tsql operator-like cash-sensitivity

2 answers

5


Yes it is possible, but first it is necessary to know what type of COLLATION your SQL SERVER is configured. For this Check with the following Select:

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

If you have a result like:

sql_latin1_general_cp1_ci_as

This means that Banco does not differentiate between Upper Case and Lower Case accents;

You can:

  • Change the default SQL Server grouping to a new one
  • Change the grouping of the database, but I do not advise it, because, in addition to not being easy, you will probably have to migrate from an old database to a new one

  • Create columns using a grouping other than the pattern Change grouping directly (the same syntax is used to create columns with different groupings) 

    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 very large and very, very confusing, is to create a new database with the right grouping.

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 grouping , then migrate all information by selecting the old database and inserting it into the new one using the grouping clause 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.

0

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

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

The search options:

  • Select * from names Where name like 'Rog%'
  • Select * from names Where name like 'ROG%'
  • Select * from names Where name like 'Rog%'

Being "insensitive" all searches work... But if you add this: "Collate Sql_latin1_general_cp1_cs_as" (Sensitive) only the first will work, So:

  • 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

Browser other questions tagged sql-server tsql operator-like cash-sensitivity

You are not signed in. Login or sign up in order to post.