CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
Syed Imran Shah
Syed Imran Shah

Reputation: 43

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

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)

Upvotes: 0

Views: 2536

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272306

PATINDEX is available in SQL Server 2000. Use it:

SELECT val, SUBSTRING(val, PATINDEX('%[0-9]%', val), LEN(val)) AS newval
FROM (
    SELECT '1430112345679' AS val UNION ALL
    SELECT 'PO02456977785' UNION ALL
    SELECT 'C035343542654' UNION ALL
    SELECT 'QPD1236548'
) AS x

Result

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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)

Upvotes: 6

Related Questions