• Nagesh (unregistered)

    In our dev shop in Hyderabad this is know as NoSQL solution. Client look at SQL code and say "no" many times.

  • Cujo (unregistered) in reply to dguthurts

    Precisely. I could see this SQL as a ONE TIME rilly quick and rilly dirty conversion to fix someone who put the full name in one field and is trying to move it to frist, muddle and lust naems. I'm betting that this is not the case.

    (All misspellings deliberate for the spelling gestapo!)

    I get code like this all the time and the chief reason I'm told was that it had to be done quickly. Those are the folks who get the code thrown back in their faces to fix on production systems when they eat up 80% of the system. "I put too much time in this to rewrite it from scratch, I can't even remember what it does!"

  • (cs)

    This post calls for a "bloody hell" button.

  • (cs)

    Please, for the sake of the children, open a SQL training manual at the CASE expresion and then use it to paper-cut this idiot to death.

  • spaceman (unregistered)

    can someone please post a statement needed to create and seed the tables this statement uses. i have this strange compulsion to actually run this and see it in action.

  • Mot (unregistered)

    John Van Wyk John Van III John Van 3rd

    Okay, 'Van' by itself is a rare last name but not unknown. There is NO WAY of figuring out from just the spaces which of the above is part of the last name and what is suffix.

    I went through this exercise with a 80,000 name DB one time. I got everything to the right of the right-most space and compared it to a table of known values, and kept adding to that table from the examples in my table. (I got as far as "5th" but Henry 8th was dead by then, or at least didn't register with our company.)

    I could do this without fear because I had a known universe of names. If I had to accept new names, it would not have worked.

    Luckily the reason I did this exercise was to move to a new app that had a separate "name suffix" field.

  • urgh (unregistered)

    How can a company hire someone that codes like this? If your code contains 100 snippets of almost-identical stuff, it's the HUGE red flag that you are a total beginner that has never coded in his life.

  • urgh (unregistered)

    How can a company hire someone that codes like this? If your code contains 100 snippets of almost-identical stuff, it's the HUGE red flag that you are a total beginner that has never coded in his life.

  • urgh (unregistered)

    Damn Parkinson.

  • Ryan (unregistered) in reply to iToad
    iToad:
    String processing with SQL. What could possibly go wrong?
    Amen
  • golddog (unregistered) in reply to dogmatic
    dogmatic:
    Ouch! Even if you didn't know about BETWEEN you would at least google to see if something like that exists instead of writing this monstrosity.

    Not really on topic, but one of my pet peeves with SQL (at least the variants with which I've worked) is that between is ordered.

    Zero should be between 1 and -1 just as it's between -1 and 1. But no.

  • Indeedly (unregistered) in reply to iToad

    I never comment here, but seeing this code made a void in my soul and I had to share it.

  • lietu (unregistered)

    So you're saying that's a glorified version of:

    SELECT SUBSTRING(name, 1, LOCATE(' ', name) - 1) AS name, SUBSTRING(name, LOCATE(' ', name) + 1) AS suffix FROM CLINICIAN;

    ?

  • Old fart (unregistered)

    You should post a warning label before exposing us unsuspecting people to such a cruel sample of code. That's just not right!

  • Paul Neumann (unregistered) in reply to Indeedly
    Indeedly:
    I never comment here, but seeing this code made a void in my soul and I had to share it.

    You are not allowed to share void. It can only be accessed through the VoidFactory accessor class.

    captcha: iusto - iusto think the captcha's were funny, not so much now.

  • Joe (unregistered)

    I think this SQL can be optimized.

  • Mango Cream (unregistered)

    This is why I hate string manipulations in SQL. (A)/

  • TallMan (unregistered)

    This must be someone paid based on LOC. And doing a pretty good job at it! Making 2k lines out of something that should be 10. 200x higher wages than doing it properly!

  • Quidam (unregistered) in reply to iToad

    String processing with SQL. It already went wrong.

  • Ziplodocus (unregistered) in reply to phelmer

    If that captcha is true, what is the definition of a cumquat?!

  • mike (unregistered)

    I think I know the guy that wrote this. It's that guy with the worn out ctrl, c, and v keys on his keyboard isn't it?

  • Patrick (unregistered)

    It goes on forever — and — oh my God — it's full of stars!

  • Andrew (unregistered)

    jeebus.... I know that is SQL server code but surely it could be done with instring or something.

    here's it done in Oracle with regex

    SQL> with t as (select 'HARTLEY JR' txt from dual) 2 select regexp_substr(txt,'^[^ ]+') first_string 3 ,regexp_substr(txt,'[^ ]+$') second_string 4 from t;

    FIRST_S SE

    HARTLEY JR

    1 row selected.

  • Philipp (unregistered)

    I have never worked with SQL but I get a slight suspicion that this can be done more efficiently.

  • David (unregistered) in reply to hikari
    hikari:

    It's a page. Handling names is certainly filled with complex issues, most of which he touches on, but there's a certain point at which a programmer can blow off the concerns. You don't have a name or not one that's representable in Unicode? That's your problem. Likewise, that many programs have too short a length for names is arguable; that programmers seriously need to worry about unbounded names is not.

  • jtf (unregistered)

    Pure and simple design issue. Break out the individual columns in the target table and do the splits in the ETL code.

  • Sander (unregistered)

    That seriously looks like some awful generated SQL. Nobody could've typed that manually.

  • roselan (unregistered)

    Most indian coders are paid by the line.

    That's a smart way to add a few bucks to your wage, if you are lucky enough to work for Infosys, Satyam or EDS... i guess.

  • lonetaco (unregistered)

    Wow. He really is a guru. You should study hard and learn from him.

  • wkmanire (unregistered)

    What...How.... dammit! Come on! At what point do you stop and consider that you may be doing it wrong!?

  • Efficiency Expert (unregistered)

    So this is what happens when SQL does the splits.

    This is why I always recommend stretching.

  • Jan (unregistered)

    If all you have is a hammer, every problem looks like a nail.. and as an added bonus, you're a crap carpenter..

    Couple of month's ago we had a tech-talk by a "xml-guru" boasting about xml databases: "There great, you can even build a webserver with them!"

  • Dan (unregistered)

    I almost just screamed out loud at work while scrolling through that.

  • Brian (unregistered)

    Finally a robust solution to splitting a string in T-SQL!

  • PITA (unregistered)

    Other than all of the carriage returns, I don't see any problems.

  • TJ Powell (unregistered) in reply to iToad

    When I see these, I load up my briefcase - tell them I can't (won't) help them, and leave. Life is too short. I had one of these "opportunities" very recently, and I PASSED. Quickly. Makes my stomach hurt to look at it. Before I left the guy argued with me that I had written some SQL code that wasn't a join, since all of his were 8-60 lines long - and mine was 2 lines, and didn't have the word "join" in it. (It was a simple "where" clause on with a select on two tables.) He could not understand that there is more than one way to write a simple pivot "join" between two tables. lol

  • TJ Powell (unregistered) in reply to TJ Powell

    The answer to when you know is after 1.5 "screen" pages. If it's long than that, it's wrong.

  • Conrad (unregistered) in reply to Mot

    Actually you can solve it. JosephStyons was able to do it with this answer to the StackOverflow question How can I parse the first, middle and last name from a full name field in SQL?

    Here's a SQL Fiddle that demonstrates it

  • JoI hope this is a jopkee Celko (unregistered) in reply to iToad

    I hope this is a joke. There is nothing right in it; nothing! At one consulting gig decdes ago, I suggested that we find all the code written by one guy, throw it out and start over without even bothering to read it. The client nodded.

    I then suggested that we run over this guy in parking lot; the client and the other programmers all smiled and two of them got out their car keys.

  • Joe Celko (unregistered) in reply to Andrew

    I advise people to get a package like Melissa Data to clean up names and addresses. Life is too short to re-invent the wheel and to worry about "Dr. Jean-Paul van der Poon MD" gettign his mail.

  • barfo rama (unregistered) in reply to Some Jerk
    Some Jerk:
    Warren:
    If you even start writing this without thinking "there must be a better way", you're not a programmer, just a typist that knows SQL.

    Correction... a typist who happens to know a website that displays sql code.

    A secretary is a ho that can type. An executive secretary is a typist that can screw. A SQL programmer is a secretary who picks up tricks on Hollywood Blvd and squeals in delite.

    captcha: appellatio

  • barfo rama (unregistered) in reply to Mick
    Mick:
    Evan:
    Chronomium:
    iToad:
    String processing with SQL. What could possibly go wrong?
    Indeed, this is clearly one of those cases where you're using a screwdriver to mix cake batter.
    I've used pliers to eat a pancake before.

    (It was a camping trip and we had a shortage of silverware because we're stupid, but I had my Leatherman.)

    If your Leatherman doesn't have a fork in it, you should trade it in and get a Swiss Army Knife.

    He used the fork to keep his eye from twitching after he saw this code.

    Captcha: similis

Leave a comment on “SQL Splits”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article