CODEWITHSUNDEEP
sqlfunctiont-sqlstored-procedures
JVGBI
JVGBI

Reputation: 575

Leftrimming Zero's with SQL with chars included in string

I need to write a function that removes all leading zero's from a varchar column. Example: 0300 has to become 300 and A0300 has to become A300. The first one is quit easy, but I can't get the second one to work (A0300-> A300). Could anyone point me in the right direction?

Upvotes: 0

Views: 63

Answers (3)

HABO
HABO

Reputation: 15852

The festering code below demonstrates one way to walk through the strings with a trivial state machine for each and parse out the evil zeroes. To see what is happening internally you can switch selects after the CTE.

-- Sample data.
declare @Samples as Table ( SampleId Int Identity, Sample VarChar(100) );
insert into @Samples ( Sample ) values
  ( '0300' ), ( 'A0300' ), ( 'zer0' ), ( '0' ), ( '000' ), ( '00000Q050098' );
select * from @Samples;

-- Fiendish thingy.
declare @False as Bit = 0, @True as Bit = 1;
with
  Characters as (
    select SampleId, Sample, 1 as Position, Substring( Sample, 1, 1 ) as Character,
      case when Substring( Sample, 1, 1 ) = '0' then @True else @False end as IsZero,
      case when Substring( Sample, 1, 1 ) = '0' then @True else @False end as FirstZeroes
      from @Samples
    union all
    select SampleId, Sample, Position + 1, Substring( Sample, Position + 1, 1 ),
      case when Substring( Sample, Position + 1, 1 ) = '0' then @True else @False end,
      case
        when FirstZeroes is NULL then NULL -- We're done with this string.
        when FirstZeroes = @True and Substring( Sample, Position + 1, 1 ) <> '0' then NULL -- We just finished with this string.
        when Substring( Sample, Position + 1, 1 ) = '0' then @True -- We're (still) going with this string.
        else @False end
      from Characters
      where Position < Len( Sample ) )
--  select * from Characters order by SampleId, Position; -- Use this statement to see the intermediate results.
  select C.SampleId, C.Sample,
    Coalesce( Stuff( ( select Character from Characters where SampleId = C.SampleId and ( FirstZeroes = 0 or FirstZeroes is NULL ) order by Position for XML path(''), type).value('.[1]', 'VarChar(max)' ), 1, 0, '' ), '' ) as DeZeroed
    from Characters as C
    group by SampleId, Sample

Upvotes: 0

JeromeE
JeromeE

Reputation: 469

On older versions where PATINDEX does not work, or if PATINDEX is too slow:

Do a case and use LIKE '[0-9]' / NOT LIKE '[0-9]' to find the right splitting point - assuming the know the max length of your string so that you can prepare as many cases as needed. You find the right the right character number where the number starts, and cast the right part as INT to eliminate the leading 0, then cast the result as VARCHAR to re-aggregate with your leading letter(s).

Would make something like below: SELECT CASE WHEN LEFT (columnname,1) NOT LIKE '[0-9]' AND SUBSTRING (columnname,2,1) LIKE '[0-9]' THEN LEFT (columnname,1) + CAST (CAST (SUBSTRING (2,(LEN(columnname)-2) ) AS INT) AS VARCHAR(25)) WHEN LEFT (columnname,2) NOT LIKE '[0-9]' AND SUBSTRING (columnname,3,1) .... END

You should trim the spaces with LTRIM(RTRIM(columnname)) if you're not sure of the leading/trailing spaces, since we are counting the number of characters it may be more reliable.

Upvotes: 0

j.v.
j.v.

Reputation: 997

WITH test AS
(
    SELECT 'A0300' AS code
    UNION
    SELECT '0300'
), 
strip AS (
SELECT code, SUBSTRING(code, PATINDEX('%[0-9]%', code), LEN(code)) AS number
from test
)

select REPLACE(code, number, CAST(number as INT)) 
FROM strip

Upvotes: 1

Related Questions