How to check that first one or first two or first three characters in a value is alphabet

Question

I have table with IDNumber column which contains either all numeric or alpha-numeric where either first or first two or first three characters are alphabet. All I want to do remove those alphabets from that value.

Please note that database is in SQL Server 2000.

For example:

1430112345679 (This is OK)
PO02456977785 (Remove first two letters)
C035343542654 (Remove first letter)
QPD1236548    (Remove first three letters)

Answer 1

This should do what you want in any supported verion of SQL Server:

select idNumber, stuff(idNumber, 1, patindex('%[0-9]%', idNumber) - 1, '')

Here is a db<>fiddle.

In SQL Server 2000 -- which has been unsupported for a long, long time -- you can do something more brute force:

select (case when idNumber like '[0-9]%' then idNumber
             when idNumber like '_[0-9]%' then substring(idNumber, 2, len(idNumber))
             when idNumber like '__[0-9]%' then substring(idNumber, 3, len(idNumber))
             when idNumber like '___[0-9]%' then substring(idNumber, 4, len(idNumber))
             . . . -- however many clauses you need
        end)

or using LIKE:

select (case when left(idNumber, 1) between '0' and '9' then idNumber
             when substring(idNumber, 2, 1) between '0' and '9'
             then substring(idNumber, 2, len(idNumber))
             when substring(idNumber, 3, 1) between '0' and '9'
             then substring(idNumber, 3, len(idNumber))
             when substring(idNumber, 4, 1) between '0' and '9'
             then substring(idNumber, 4, len(idNumber))
             . . . -- however many clauses you need
        end)