Flags in SQL Server Regular Expressions – Case Sensitivity

Part 6 of Learn RegEx With Louis

In this week’s sixth entry of my learning RegEx series, I am going to do two last intro entries for a while, this one on case sensitivity, and another on multi and single line searches. After this I will move into all of the functions that are available in SQL Server 2025 and Azure SQL (and I will come back if I learn any additional things that we need to cover either right after that, or anytime I learn something new I want to share about RegEx).

There are currently only 4 flags that SQL Server supports and they are used to change soem of the fundamental ways that the expressions are applied. These flags are:

i – insensitive
c – case sensitive
m – ^ and $ match end of line, not entire string
s – single line, dot matches newline

There are other flags you might find in your works with RegEx that are not allowed as part of the SQL Server implementation. Using any of these or something invalid will cause an error.

g – global, dont return after first match
x – extended, ignore whitespace
J – changed – allow duplicate subpattern names
A – anchor to start of pattern
U – ungreedy
u – match with fully Unicode

The whole concept of “greedy” and “ungreedy” expression is something I will try to cover in the future, but as far as I understand, ungreedy means something like .+ would stop at one match of any character, and greedy will match as many items until it reaches the end of matches. global seems to already match how SQL Server returns all matches when working with the REGEXP_MATCHES function to return all matches that it findes. The others I am not going to pretend to understand for now. I might finish off with the series covering other types of RegEx implementations…Maybe.

In this fifth section of the series, Let’s look at the i and c flags.

Setup

As we have already discussed, the default case sensitivity for RegEx is case sensitve. In SQL Server that is the default too. However, that case sensitivity doesn’t mean that comparisons are all case sensitive, as they will compare to the data they compare with based on the collation of the data. The create for the table I will be using is in the Appendix, and I will load some data into that table to show how case works.

TRUNCATE TABLE RegExTest;
INSERT INTO RegExTest
VALUES ('UPPERCASE'),('lowercase'),('MixedCase');

You can see the collation of the columns in your table using the INFORMATION_SCHEMA.COLUMNS view, like this:

SELECT COLLATION_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'dbo'
  AND  TABLE_NAME = 'RegExTest'
  AND  COLUMN_NAME = 'Value'

For me this returns:

COLLATION_NAME
--------------------------------------------
SQL_Latin1_General_CP1_CI_AS

You can see in the name that it is case insensitive as well as accent sensitive. I won’t demonstrate accent sensitivity, but that would follow the collation as well.

Yes, I should have installed with a Windows collation, but I just took the defaults and this has long been the default in Windows.

To force case sensitivity in some examples, I will collate the column value like such:

--No match, as the value is uppercased
SELECT  Value
FROM   RegExTest
WHERE VALUE COLLATE Latin1_General_100_BIN2 like 'uppercase'

--This will work, because the value matches perfectly
SELECT Value
FROM   RegExTest
WHERE VALUE COLLATE Latin1_General_100_BIN2 like 'UPPERCASE'

This returns:

Value
-----------------
UPPERCASE

Note that when I said “perfectly”, even adding a space to the end of the 'UPPERCASE ' will cause it not to match, which is not how SQL Server comparisons typically behave.

The c Flag – Forcing Case Sensitivity

As a reminder, my column is in 'SQL_Latin1_General_CP1_CI_AS' collation. So if I execute the following query:

SELECT Value
FROM   RegExTest
WHERE  REGEXP_LIKE(value,'UpperCase');

It still matches, so it returns:

Value
-----------------------
UPPERCASE

As a quick aside with comparisons.nNote that even though the comparison is case insensitive, you still need to match the final character if you are using a simple equality match so:

SELECT Value
FROM   RegExTest
WHERE  REGEXP_LIKE(value,'UpperCase ');

Nothing is returned, if you need to match a variable amount of whitespace in a value, you need to something like UpperCase[ ]? that will match zero or more spaces on the end of a string, or UpperCase{/s]? to allow any whitespace characters.

If you want to make sure the comparision is case sensitive, you can use the 'c' flag. Flags are added using a third parameter of the REGEXP_ family of functions.

SELECT Value
FROM   RegExTest
WHERE  REGEXP_LIKE(value,'UpperCase','c');

Nothing is returned. Now you need to match the data using the following:

SELECT Value
FROM   RegExTest
WHERE  REGEXP_LIKE(value,'UPPERCASE','c');

Now the row with the value UPPERCASE is returned.

The `i` Flag – Forcing Case Insensitivity

As we have noted, the default is case sensitive, so if you have case sensitive data that you want to compare, this is the power of the i flag. For example, let’s look for the value 'lowercase' in the sample data.

SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value COLLATE Latin1_General_100_BIN2,
                                               'LowerCase')

Nothing is returned. To force case insensitivity:

SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value Collate Latin1_General_100_BIN2,
                                             'LowerCase','i');

Now this returns:

Value
-----------------
lowercase

A couple of odds and ends about flags

It this section I will cover a few things that will help you when working with flags.

Multiple flag Values

It is allowed to pass in multiple flags. If they are mutually exclusive, for example a flag value of 'im', it would set the i and m flags. More on this in the next entry, but if the flags are not mutually exclusive, the last one is taken. For example:

SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value, 'LowerCase','ic');

No rows returned, because it’s case sensitive. But make it ci:

SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value, 'LowerCase','ci');

This returns:

Value
----------------
lowercase

Invalid flags

If you pass an invalid flag (which can be the uppercase version of a valid flag, as flags are case sensitive):

SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value, 'LowerCase','I');

This causes the following error:

Msg 19303, Level 16, State 0, Line 119 Invalid flag provided. 'I' are not valid flags. Only {c,i,s,m} flags are valid.

Really repeating flags

What is fun is that as long as the flag values are legal, they can be repeated over and over:

--This DOES work
SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value, 'LowerCase','ccccciiiiiii');

The last value in the string is i, so it is insensitive. c and i are mutually exlusive, but you can mix with m and s, which I will show in the next section.

Flags and Variables

Finally, you are allowed to us a variable for the flags, which is a great value of flags being a string rather than syntax.

DECLARE @flags varchar(10) = 'i';

SELECT Value
FROM   RegExTest
WHERE REGEXP_LIKE( Value, 'LowerCase',@flags);

This returns:

Value
-----------------------
lowercase

Summary

In this sixth entry in the RegEx series, we looked at case sensitivity using the c and i flags. I don’t expect this to be used much in my typical work, because most of the time I am keen for searches to be case insensitive, and almost every database and column I work with is case insensitive.

I also looked at how you can use multiple flags, put your flags in a variable, and what happens when you use an invalid flag.

If you have read any of the previous entries, I am starting to feel myself getting the hang of some things. Not one of the things in Regular Expressions has come naturally, but with repetition it is doable. That and I definitely suggest keeping some notes… or use mine if you want to keep it simple :).

Appendix

USE TempDB;
GO

SET NOCOUNT ON;

-- Drop the table if it exists
IF OBJECT_ID('dbo.RegExTest', 'U') IS NOT NULL
DROP TABLE dbo.RegExTest;

-- Create the table
CREATE TABLE dbo.RegExTest (
RegExTestId INT IDENTITY(1,1) CONSTRAINT PK_RegExTest PRIMARY KEY,
Value NVARCHAR(100) CONSTRAINT AK_RegExTest UNIQUE
);