patternsqlMinor
Removing a string prefix
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
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
The string of numbers will always start with
101 then it will have a set of 0s and a set of random numbers. Example:1010000123456I 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 @tCode 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 @tContext
StackExchange Database Administrators Q#72850, answer score: 3
Revisions (0)
No revisions yet.