Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Remove the first 3 characters and then use PATINDEX(). –  ypercube Jul 31 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 at 14:25
    
Cheers! Worked perfect. Thank you! –  d4n743m4n Jul 31 at 14:33
    
The string will always be 13 digits. It seems to be working in my query now. –  d4n743m4n Jul 31 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 at 16:50

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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