Removing a string prefix

Question

I have a string of numbers that I need to trim a portion from using TSQL.

The string of numbers will always start with 101 then it will have a set of 0s and a set of random numbers. Example:

1010000123456

I need to trim the 101 and the set of zeros. This is probably simple but I'm having all kinds of issues because I don't have a specific character to reference to using a CHARINDEX and the possible combination of a 001 when the random numbers start that I need to keep is giving me issues using a PATINDEX with a SUBSTRING.

Answer 1

The SUBSTRING(n, 3+PATINDEX('%[^0]%', SUBSTRING(n, 4, LEN(n))), LEN(n)) expression should work.

• first, it strips the first 3 characters: SUBSTRING(n, 4, LEN(n))
• then it use PATINDEX() with the '%[^0]%' pattern to locate where the digits after the 0s start: PATINDEX('%[^0]%', SUBSTRING(n, 4, LEN(n)))
• then it uses SUBSTRING() and the previously found number (+3), to keep only the wanted digits (from the patindex number found up to the end.)

Test at SQL-Fiddle

Answer 2

There are other ways to do it, eg STUFF or just some simple integer maths, but these do make certain assumptions about the string, eg

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourString CHAR(13) )

INSERT INTO @t VALUES 
    ( 1010000123456 ),
    ( 1010001234567 ),
    ( 1010012345678 ),
    ( 1010123456789 ),
    ( 1011234567890 )

SELECT
    CAST( STUFF( yourString, 1, 3, 0 )  AS INT ) [stuff],   -- 2 functions
    CAST( yourString  AS BIGINT ) - 1010000000000 do_math,  -- 1 function
    SUBSTRING(yourString, 3+PATINDEX('%[^0]%', SUBSTRING(yourString, 4, LEN(yourString))), LEN(yourString)) AS ypercube     -- 5 functions
FROM @t

Answer 3

For the sake of completeness, you could use the following:

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourString CHAR(13) )

INSERT INTO @t VALUES 
    ( 1010000123456 ),
    ( 1010001234567 ),
    ( 1010012345678 ),
    ( 1010123456789 ),
    ( 1011234567890 )

SELECT CAST(RIGHT(yourString, LEN(yourString)-3) AS BIGINT)
FROM @t;

This presumes you always have a string that is at least 3 characters long, and removes the left-most 3 characters, turning the remaining string into a BIGINT to remove the leading zeros.

props to @wBob for providing the DDL.