3

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.

  • Remove the first 3 characters and then use PATINDEX(). – ypercubeᵀᴹ Jul 31 '14 at 14:19
  • This would work I think: SELECT SUBSTRING(n, 3+PATINDEX('%[^0]%', SUBSTRING(n, 4, LEN(n))), LEN(n)) (where n is the string.) – ypercubeᵀᴹ Jul 31 '14 at 14:25
  • The string will always be 13 digits. It seems to be working in my query now. – d4n743m4n Jul 31 '14 at 14:34
  • @PaulWhite Yes there will always be more than one zero but it's random based on how long the following set of [1-9] numbers are. – d4n743m4n Jul 31 '14 at 16:50
  • In that case RIGHT(@s,LEN(@s)-PATINDEX('%00[^0]%',@s)-1) would also work. – Paul White Jul 31 '14 at 17:08
2

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

3

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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.