Post reply

I need my columns to trim off a few pounds...!

  • March 27, 2012 at 11:56 am

    #253737

    Okay Guru's, I got one for you!

    I believe I'm on the right path, but need some Syntax assistance.

    I have a table with a column called SLIP_NUMBER. SLIP_NUMBER, in this table, is a VARCHAR(30) so users (aka, The Root of all EVIL) entered in all kinds of funny characters like *, &, %, -, etc. It is wreaking havoc on my SQL (see below).

    What I want to do is, trim off ANY asterisks from the LEFT AND trim off ANY dashes (-) PLUS any data after the dash on the RIGHT. Some values I see have a -HH* or -HH, so I want the -HH* and -HH to be dropped. 😀

    Msg 245, Level 16, State 1, Line 12

    Conversion failed when converting the varchar value '*12345' to data type int.

  • March 27, 2012 at 11:58 am

    #1465243

    Just use nested replace.

    replace(Replace(MyField, '*', ''), '-', '')

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 27, 2012 at 12:00 pm

    #1465244

    Can we get sample data please?

  • March 27, 2012 at 12:14 pm

    #1465267

    ColdCoffee (3/27/2012)

    Can we get sample data please?

    And expected results based on the sample data.

  • March 27, 2012 at 12:29 pm

    #1465278

    ilike doing these types of things in steps to verify what I have done:

    USE test

    CREATE TABLE #test (gobbledegook VARCHAR(30))

    INSERT INTO #test

    SELECT '*12345'

    UNION ALL

    SELECT '*6789-hir'

    UNION ALL

    SELECT 'hello-john'

    SELECT gobbledegook, CHARINDEX('*', gobbledegook, 1)

    FROM #test

    UPDATE #test

    SET gobbledegook = SUBSTRING(gobbledegook, 2, LEN(gobbledegook))

    WHERE CHARINDEX('*', gobbledegook, 1) = 1

    SELECT gobbledegook, CHARINDEX('-', gobbledegook, 1)

    FROM #test

    UPDATE #test

    SET gobbledegook = SUBSTRING(gobbledegook, 1, CHARINDEX('-', gobbledegook, 1) - 1)

    WHERE CHARINDEX('-', gobbledegook, 1) > 1

    SELECT * FROM #test

    Jared
    CE - Microsoft

  • March 27, 2012 at 1:54 pm

    #1465313

    Thank you Everyone,

    The REPLACE worked; however, after running the query, I found thousands records with all kinds of non-numeric values. That's is a lot of REPLACE(REPLACE(REPLACE...

    So, I turned it back to the customer for data clean up.

    thanks again!

  • March 27, 2012 at 2:16 pm

    #1465332

    SQL_Enthusiast-AZ (3/27/2012)

    Thank you Everyone,

    The REPLACE worked; however, after running the query, I found thousands records with all kinds of non-numeric values. That's is a lot of REPLACE(REPLACE(REPLACE...

    So, I turned it back to the customer for data clean up.

    thanks again!

    That sounds like the best solution.:-D

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 27, 2012 at 9:30 pm

    #1465446

    SQLRNNR (3/27/2012)

    SQL_Enthusiast-AZ (3/27/2012)

    Thank you Everyone,

    The REPLACE worked; however, after running the query, I found thousands records with all kinds of non-numeric values. That's is a lot of REPLACE(REPLACE(REPLACE...

    So, I turned it back to the customer for data clean up.

    thanks again!

    That sounds like the best solution.:-D

    Ditto that.

    --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)

  • March 28, 2012 at 8:10 am

    #1465699

    SQL_Enthusiast-AZ (3/27/2012)

    Thank you Everyone,

    So, I turned it back to the customer for data clean up.

    thanks again!

    If that works, can I come work for you? 🙂

  • March 28, 2012 at 9:58 am

    #1465778

    Sure, when can you start?

    They started cleanup yesterday. As of this morning, there were less than 2500 records remaining... I think by the end of the week, they should be done.

  • March 28, 2012 at 10:07 am

    #1465789

    Dang.

    Last time I tried to get users to clean up data, it was more mess than what we started with! 🙂

    Granted, we feed the data from Cobol created data....

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

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