Post reply

Strip this!!!

  • November 22, 2005 at 2:04 pm

    #188604

    Does anyone have a set based way to remove preceeding zeros from a varchar field?  The number of zeros that preceed this data is widely variable too. 

    I have data that is like such...

    000H.K6

    004ghs

    00000l.sa

    lh008

     



    A.J.
    DBA with an attitude

  • November 22, 2005 at 2:41 pm

    #605329

    Forget it... I found it.

    My buddy Bill here showed me the light..

     

    select  REPLACE(LTRIM(REPLACE(columX, '0', ' ')), '', '0') AS new_col1  .....



    A.J.
    DBA with an attitude

  • November 22, 2005 at 3:12 pm

    #605335

    Are you sure thats what you need? Wouldn't this remove all zeros and not just the preceeding ones?

    DECLARE @Var table (col1 varchar(10))
    INSERT INTO @var VALUES ( '000H.K6' )
INSERT INTO @var VALUES ( '004ghs' )
INSERT INTO @var VALUES ( '00000l.sa' )
INSERT INTO @var VALUES ( 'lh008' )
    select REPLACE(LTRIM(REPLACE(col1, '0', ' ')), '', '0') AS new_col1 FROM @var

    Results in,

    H.K6
4ghs
l.sa
lh  8

     

    --------------------
    Colt 45 - the original point and click interface

  • November 22, 2005 at 3:14 pm

    #605336

    yeah... actually I found that out already...

    this is better

     

    select  SUBSTRING(column1, PATINDEX('%[^0]%', column1+'A'), LEN(column1))



    A.J.
    DBA with an attitude

  • November 22, 2005 at 3:16 pm

    #605337

    Hmm ... I recognise that solution ...

     

    --------------------
    Colt 45 - the original point and click interface

  • November 22, 2005 at 3:32 pm

    #605341

    Yup I stole it..



    A.J.
    DBA with an attitude

  • November 22, 2005 at 3:33 pm

    #605342

    select REPLACE(LTRIM(REPLACE(col1, '0', ' ')), ' ', '0') AS new_col1 FROM @var

    The second replace should be an space instead of an empty string for it to work!

    in any case I would also use a where clause for performance

    where col1 like '0%'


    * Noel

  • November 22, 2005 at 3:35 pm

    #605343

    Problem is... that replaces all spaces.  Which is no good for what I am doing.



    A.J.
    DBA with an attitude

  • November 22, 2005 at 3:45 pm

    #605345

    Well if spaces are important to you that's a different thing (you din't said that )

    definitely the patindex method is way to go ... Still use the where clause though

    Cheers,

     


    * Noel

  • November 22, 2005 at 5:37 pm

    #605357

    Why not just create a fuction?

    Supply your string as a parameter, create WHILE LEFT(@InputString, 1) = '0'...

    inside of the function and use result of the fuction in your select.

    P.S. I wrote similar prescription in another topic several hours ago.

    _____________
    Code for TallyGenerator

  • November 22, 2005 at 5:39 pm

    #605358

    because I want it to actually run fast.



    A.J.
    DBA with an attitude

  • November 22, 2005 at 5:42 pm

    #605359

    Why create a function for something that can be achieved in on T-SQL statement?

     

    --------------------
    Colt 45 - the original point and click interface

  • November 22, 2005 at 5:45 pm

    #605361

    Amen brother Phil!!!!



    A.J.
    DBA with an attitude

  • November 22, 2005 at 5:46 pm

    #605362

    It will be fast.

    There are no disk operations inside of this function, so you probably would not notice the difference.

    _____________
    Code for TallyGenerator

  • November 22, 2005 at 5:49 pm

    #605363

    Functions like that will be run for each and every row in that table I am querying though vs. once in a set based operation.



    A.J.
    DBA with an attitude

Viewing 15 posts - 1 through 15 (of 24 total)

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