SQL Server - Like/Pattern Matching

Question

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!

Answer 1

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.