CODEWITHSUNDEEP
sqlsql-serverregex
Chris
Chris

Reputation: 444

SQL Server - Like/Pattern Matching

I'm trying to implement a check constraint on a key field. The key field is composed of a 3 character prefix, and then appended with numeric characters (which can be provided manually, but the default is to get an integer value from a sequence, which is then cast as nvarchar). The key field is defined as nvarhcar(9).

I'm doing this for multiple tables, but here is a specific example below to demonstrate: Table name: Company Key field: IDCompany Key field prefix: CMP

Examples of valid keys -

CMP1
CMP01
CMP10000
CMP999999

Examples of invalid keys - 

CMPdog1
steve
1CMP1
1
999999999

The check constraint I came up with was:

IDCompany LIKE 'CMP%[0-9]'

However, this is beaten by CMPdog1 etc.

What should I be using as a check constraint to enforce an unknown number of numeric characters?

I could do the following:

IDCompany LIKE 'CMP[0-9]' OR IDCompany LIKE 'CMP[0-9][0-9]' OR .... through to 6 characters

But, this seems like a clunky way of doing it, is there something smarter?

EDIT 2: This actually doesn't work, it does not exclude negative numbers:

EDIT 1:
This solution ended up working for me:

IDCompany nvarchar(9) NOT NULL CONSTRAINT DEF_Company_IDCompany DEFAULT 'CMP' + CAST((NEXT VALUE FOR dbo.sq_Company) AS nvarchar) CONSTRAINT CHK_Company_IDCompany CHECK (IDCompany LIKE 'CMP%[0-9]' AND ISNUMERIC(SUBSTRING(IDCompany,4,LEN(IDCompany)-3))=1)

EDIT 3: Solution - As proposed in Szymon's post below.

Thanks all!

Upvotes: 3

Views: 543

Answers (4)

bjnr
bjnr

Reputation: 3437

Well, I would reconsider the design of your table and create 3 columns:

  • prefix, CHAR(3), with a default as 'CMP' and a constraint to allow only 'CMP' combination
  • id, INTEGER
  • companyid, NVARCHAR(9), a computed, persisted column as sum of the first 2 columns. Most probably with an index on.

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

Try this:

isnumeric(substring(IDCompany,4,len(IDCompany)))=1 and IDCompany not like '%[.,-]%'

How this works: The first three characters are fixed, so we only need to check from the 4th character onwards. So we get the required substring. Then, we use isNumeric to check if the substring is entirely numeric. Example here

EDIT: As pointed out in comments by Allan, we need an extra check to ensure that characters used in numeric strings like commas or dots are not part of the input string.

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You could do something like that:

where LEFT(IDCompany, 3) = 'CMP'
    and isnumeric(RIGHT(IDCompany, len(IDCompany) - 3)) = 1
    and IDCompany not like '%[.,-]%'

The first part checks that it starts with CMP

The next part is to make sure that the rest is numeric but excluding negative and decimal numbers.

Upvotes: 2

MikkaRin
MikkaRin

Reputation: 3084

Unfortunately, SQL Server doesn't suppport regular expressions.

So there is only 2 ways to solve your problem:

  1. Use CLR function for using regular expression. You may find more information here

  2. Or whrite long WHERE clause like you suggested:

    IDCompany LIKE 'CMP[0-9]' OR IDCompany LIKE 'CMP[0-9][0-9]' OR ....

Upvotes: 1

Related Questions