HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Removing a string prefix

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
prefixstringremoving

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#72850, answer score: 3

Revisions (0)

No revisions yet.