Post reply

Need T-SQL Query to separate words with spaces between them

  • June 15, 2011 at 2:58 pm

    #242121

    Alright, here's a real stupid one. My developer wants me to come up with a query to parce out a single column that has values where the words have spaces between each other. The catch is he only wants that where there is a total of 3 words (which in theory should be 2 spaces). However - this table is garbage. It's called "Fullname" but has stuff in there that absolutely does not belong in there (like FAX number, and USE 12345, and other useless, bad data). I'm trying to see if I can some how divide 3 word values in the column into 3 derived columns or something like that.

    So - here is a small sampling for you to see. What would you do? (Gail, I've already thought about shooting him and claiming self defense, but it probably wouldn't win me many points with the customer...) 😛

    Data sampling

  • June 15, 2011 at 3:04 pm

    #1339330

    I'm sorry officer, my sanity shot him before I could talk it out of saving itself...

    Alright, start with a LEN(field) - LEN( REPLACE(field, ' ', '')) = 2

    That'll locate your 2 spacers.

    Then from there you'll be looking at something like the following:

    LeftName = LEFT( field, CHARINDEX( Field, ' ') - 1),

    MidName = SUBSTRING( field, charindex( field, ' ') + 1, charindex( field, ' ', charindex( field, ' ') + 1) - charindex( field, ' '))

    RightName = REVERSE( LEFT( REVERSE( field), CHARINDEX( REVERSE( field), ' ') - 1)))

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 15, 2011 at 3:40 pm

    #1339349

    Mister Farrell sir - you ROCK!

    😀

  • June 15, 2011 at 3:56 pm

    #1339360

    BLAH! OK - here is what he really wanted...He wants a count of the records in this column called "FullName" that has 3 words in it. Sorry for the garbage here. This is just a straight text field out on a website (I just now found out). No wonder it's so junky (and while this is from the customer's DEV environment, this is what is also in PROD!)

    :w00t:

    Is there a way for me to get a count from this column of records that have a value with 3 words in it? No numbers, no more than 3 words/2 spaces?

    Thanks!

  • June 15, 2011 at 4:18 pm

    #1339368

    Um...

    SELECT COUNT(*) FROM Tbl WHERE LEN(field) - LEN( REPLACE(field, ' ', '')) = 2 AND field not like '%[0-9]%'

    ? That seems too easy.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 15, 2011 at 8:20 pm

    #1339423

    I actually wound up going with your original code from earlier, and just making it a named column. Gave it to him in Excel, and that was that. Thanks again though Craig - the code from earlier took care of it all!

    🙂

  • June 15, 2011 at 10:06 pm

    #1339442

    Rich Yarger (6/15/2011)

    So - here is a small sampling for you to see. What would you do?

    Heh... hunt the people responsible for this data travesty down and take them out for a nice pork chop dinner... Moden Style! 😛

    --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 7 posts - 1 through 6 (of 6 total)

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