Post reply

Replace Command

  • January 26, 2009 at 9:39 am

    #199284

    Maybe I didn't look hard enough, but i havent been able to find a quick solution for this and if i assume correctly (which i typically dont 🙂 ) wouldn't the following statement replace the '0' in any part of the string? I would like to remove any '0' from the beginning only, as you can see in the table sample there could be only 1 '0', but sometimes there will be 2 '0'.

    replace([Login],'0','')

    Old New

    Login Login

    02191 2191

    00551 511

    02272 2272

    00636 636

    00485 485

    02061 2061

    00705 705 - current statement would make this 75?

    00817 817

    jc1234 jc1234

    jc0123 jc0123

  • January 26, 2009 at 9:44 am

    #932674

    Just convert to INT and you're done...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 26, 2009 at 9:48 am

    #932676

    but that would be too easy 🙂

    can this be done with replace?

  • January 26, 2009 at 10:08 am

    #932691

    Sometimes REPLACE isn't the right tool for the job. I agree with Jeff, just convert to an INT.

  • January 26, 2009 at 10:12 am

    #932695

    Sorry guys,

    Just changed the problem. Please see the edited table data last two rows. Those exist and cannot be changed as they will be used.. 🙁

  • January 26, 2009 at 10:13 am

    #932696

    I did find some references online to Ltrim and Rtrim, but i dont really understand the syntax.

    edit: nevermind it looks like ltrim and rtrim only remoce spaces not characters.

  • January 26, 2009 at 10:27 am

    #932704

    Follow with what Jeff And Lynn said; repalce with an int where isnumeric is true:

    UPDATE YOURTABLE SET Login = Convert(varchar,CONVERT(INT,Login)) Where IsNumeric(Login) =1

    the rows like 'jc4444' will not be numeric, thus problem solved.

    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!

  • January 26, 2009 at 11:34 am

    #932746

    this did the trick, thanks once again everyone.

  • January 26, 2009 at 12:11 pm

    #932766

    Be careful... ISNUMERIC should never be used as an "IS ALL DIGITS" function... For example...

    SELECT ISNUMERIC('1D3'), ISNUMERIC('1E3'), ISNUMERIC('1.3'), ISNUMERIC('-13')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 26, 2009 at 12:14 pm

    #932767

    The proper way to do such a detection is similar to the following where '1D3' could be a column name...

    [font="Courier New"] SELECT 1 AS IsAllDigits

    WHERE '1D3' NOT LIKE '%[^0-9]%'[/font]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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