Post reply

sql substring help

  • February 26, 2016 at 10:00 am

    #316268

    Hi,

    sample data:

    declare @test-2 table(Code varchar(18))

    insert into @test-2

    select aa.code from (

    select '0012345678912' union all

    select '00012345678912' union all

    select '000012345678912' union all

    select '0000012345678912' union all

    select '00000012345678912' union all

    select '000000012345678912' union all

    select '21234567892345') aa(code)

    If the length of the code is > 10 then i need to remove the leading zero's till the length of th code reaches 12. i should only remove the leading zero's

    Expected result:

    012345678912 -- removed the first 0 and kept 12 as length

    012345678912 -- removed the first two 0 and kept 12 as length

    012345678912 -- removed the first three 0 and kept 12 as length

    012345678912 -- removed the four three 0 and kept 12 as length

    012345678912 -- removed the first five 0 and kept 12 as length

    012345678912 -- removed the first six 0 and kept 12 as length

    21234567892345 -- no leading zero exists so no need to remove anything.

    to start up with my side i did try with case statement and working for the first case but not sure how to do for other cases.

    SELECT case when LEN(code) = 13 and LEFT(code,1)='0' then STUFF(code,1,1,'') else code end from @test-2

    Any sample query would be much appreciated.

  • February 26, 2016 at 10:11 am

    #1861465

    SELECT

    CASE WHEN LEFT(code,1) = '0' AND LEN(code) > 10 THEN RIGHT(code,12) ELSE Code END

    from @test-2

  • February 26, 2016 at 10:35 am

    #1861475

    This is a bit safer than Gilbert's version.

    declare @test-2 table(Code varchar(18));

    insert into @test-2

    select aa.code from (

    select '0012345678912' union all

    select '00012345678912' union all

    select '000012345678912' union all

    select '0000012345678912' union all

    select '00000012345678912' union all

    select '000100012345678912' union all

    select '8912' union all

    select '0008912' union all

    select '21234567892345') aa(code);

    SELECT Code,

    CASE WHEN code LIKE '0%' AND LEN( code) > 10 AND LEN( CAST( code AS decimal(18,0))) < 12

    THEN RIGHT( CAST( 1000000000000000000 AS decimal(19,0)) + code, 12)

    WHEN LEN( code) > 10

    THEN SUBSTRING( code, PATINDEX( '%[^0]%', code), 18)

    ELSE code END

    FROM @test-2;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 26, 2016 at 11:08 am

    #1861486

    i would do it similar to the way Luis is: remove ALL leading zeros by converting to decimal, and then add as many zeros as needed to make it a length of 12.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 26, 2016 at 11:35 am

    #1861499

    Thanks a lot guys for your suggestion and solution. Appreciated....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply