SQL Server regular expression constraint

Question

I create a table by query below

CREATE TABLE testing(
    test_field VARCHAR(20) CHECK(test_field like '^[a-zA-z0-9]{6,20}$')
);

the regular expression is working in Javascript and the table was successfully created, but when I trying to execute the insert query below, the error occurred.

INSERT INTO testing VALUES('abcde12345');

The error message is listing below

The INSERT statement conflicted with the CHECK constraint "CK_testing_test_field__173876EA". The conflict occurred in database "TEST", table "dbo.testing", column 'test_field'.

Is there any difference between using Regular Expression in Javascript and SQL Server?

Any pro can help???

See article here: http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-does-support-regular-expressi — David Brabant, Jun 27 '12 at 09:38
David, as far as I can see that just checks with a `LIKE` wildcard expression and their use case is simple enough for that. This one isn't. — Joey, Jun 27 '12 at 09:39
http://stackoverflow.com/questions/267921/regular-expressions-in-sql-server-servers Not sure if you are actually activating the regex feature, though. — nhahtdh, Jun 27 '12 at 09:40
@Joey: yes, this is just an article to complement your own answer. — David Brabant, Jun 27 '12 at 09:41
So far, I've only found one DBE that supports posix regex constraints and it is Oracle (Reference: http://stackoverflow.com/questions/35157662/is-there-a-database-engine-that-allows-for-queriable-field-constraint-specified). MS SQL and DB2 look like they have their own wildcard variant, which is unfortunate as you loose consistency of constraints between layers :( — James Oravec, Feb 02 '16 at 16:50
Relevant QA on the DBA site... https://dba.stackexchange.com/q/266438/3690 — Martin Smith, May 06 '20 at 21:08
see https://stackoverflow.com/questions/41781977/regex-for-mssql-check-constraint — afruzan, Feb 01 '21 at 15:32

Joey · Accepted Answer · 2012-06-27T09:43:39.553

LIKE does not use regular expressions.

You can add regular expression support to SQL Server by creating your own functions. One method via .NET is detailed in this article.

In your case you can also try the following, but it's not pretty:

   test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
OR test_field LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'

then what should i do if i want to add an regex in constrant？ — Vernon Deng, Jun 27 '12 at 09:38

score 1 · Answer 2 · answered Apr 05 '16 at 10:38

There's a workaround for this. Since you want to check every character, you can generate the regex with replicate:

SET @pattern = replicate('[a-zA-Z0-9]', LEN(@VAL));

This is a full function around this hack:

create function dbo.example (@VAL varchar(32))
RETURNS bit
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @pattern varchar(512);
    SET @pattern = replicate('[a-zA-Z0-9]', LEN(@VAL));
    IF @VAL like @pattern
    BEGIN
        RETURN 1;
    END;
    RETURN 0;
END;
GO

score 0 · Answer 3 · answered Jun 27 '12 at 13:18

0

Sql Server does not support POSIX regular expressions, but only a subset.

See here for details.

answered Jun 27 '12 at 13:18

Alberto De Caro

5,147
9
47
73

3

`LIKE` expressions are not a subset of any regex flavour. They work differently (i.e. the whole concepts of tokens and quantifiers is absent). – Joey Jun 28 '12 at 05:51
1

LIKE pattern can use wildcards that matches "Any string of zero or more characters.", "Any single character within the specified range ([a-f]) or set ([abcdef])", "Any single character not within the specified range ([^a-f]) or set ([^abcdef])". IMHO this is a subset of regex character set and braket expressions. – Alberto De Caro Jun 28 '12 at 06:07
The wildcards `%` and `_` can be represented in regular expressions with `.*` or `.` but especially the former has a single meaning in wildcards whereas it's a token with a quantifier in regex. To me that's two very different concepts (yes, the bracket expressions are identical). Of course wildcards can describe a set of languages that is a subset of regular languages, but that doesn't mean the language used to describe them is a subset of regular expressions. IN my eyes it's not because it uses very different syntax (and even different metacharacters). – Joey Jun 28 '12 at 06:12

score 0 · Answer 4 · answered Jan 22 '21 at 07:52

Another way in tsql besides creating own functions is to create an XML Schema with a pattern constraint containing the regex:

CREATE XML SCHEMA COLLECTION dbo.RegexValidatorSchema
AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
           elementFormDefault="qualified"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="test">
        <xs:simpleType>
            <xs:restriction base="xs:string">
                <xs:pattern value="^[a-zA-z0-9]{6,20}$"/>
            </xs:restriction>
        </xs:simpleType>
    </xs:element>
</xs:schema>';

and add a check constraint so inserts/updates are validated:

ALTER TABLE dbo.testing
 ADD CONSTRAINT CK_ValidateValueMatchesPattern 
 CHECK (CAST(ISNULL('<test>' + REPLACE(REPLACE(REPLACE(Value, '&','&amp;'), '<','&lt;'), '>','&gt;') + '</test>','') AS XML(dbo.RegexValidatorSchema)) IS NOT NULL)

but be aware of performance degradation.

SQL Server regular expression constraint

4 Answers4

Linked