Home Forums SQL Server 2005 SQL Server 2005 General Discussion charindex help RE: charindex help

  • June 12, 2009 at 7:36 pm

    #1008969

    Terri (6/10/2009)

    Good explanation Jeff. A small point, your solution fails if there is more than 1 space between the 2 words. e.g 'Lincoln Lincoln' has length 16. So the left half is 'Lincoln ' and the right half is ' Lincoln'.

    This is fixed by adding RTRIM and LTRIM to left and right half respectively:

    WHEN RTRIM(LEFT(Address,LEN(Address)/2)) = LTRIM(RIGHT(RTRIM(Address),LEN(Address)/2))

    Of course, there are probably other scenarios that don't work, but the OP needs to test with his data. 🙂

    Yep... good enhancement. The best thing, of course, would be to pork chop the silly person who made the mistake of combining the data to begin with. 😉 Solutions for to correct these types of mistakes are bound to have a flaw here and there.

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