SQL – SELECT query where case insensitive and wildcard

by

Below you’ll find two ways to search an SQL table column for a case insensitive string. Both examples use the % wildcard to give you records that contain the string, rather than equal.

Upper or lower

The first example requires you to change the column to upper or lower, then search for your string in the case case.

SELECT * FROM table WHERE upper(column_name) LIKE '%ABC%'

COLLATE

This example uses COLLATE to specify how the string is compared with the records.

SELECT * FROM table WHERE column_name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%ABC%'

The command above specifies

  1. latin1

    – makes the server treat strings using charset latin 1, basically ascii

  2. CI

     – case insensitive comparisons so ‘ABC’ would equal ‘abc’

  3. AS

    – accent sensitive, so ‘ü’ does not equal ‘u’