SQL Splits

  • Esse 2012-08-01 10:02
    If you are going to do it dirty, at least use a loop and create dynamic SQL statements.
  • Kriis 2012-08-01 10:02
    Wow... just... wow
  • Not a code monkey 2012-08-01 10:03
    You can do this in one line in Haskell
  • Kriis 2012-08-01 10:04
    Esse:
    If you are going to do it dirty, at least use a loop and create dynamic SQL statements.


    Loop? This saves the processing power required to do that. Talk about optimized. I want to learn from this guy.
  • Tim 2012-08-01 10:05
    Kriis:
    Wow... just... wow

    likewise
  • Warren 2012-08-01 10:09
    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.
  • Some Jerk 2012-08-01 10:11
    Ugggh!
    Blah Blah Blah : Paid by the line
    Blah Blah Blah : The real WTF is <Insert Ignorant Statement Here>

    Yes... this is someone you should really learn from. If you can master this talented individuals' skills, you may even be able to work for Able's Car Wash writing a foxpro database to total daily inclome!
  • Some Jerk 2012-08-01 10:12
    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.
  • Ozzy 2012-08-01 10:13
    TLDR

    captcha: minim (oh the irony)
  • Andrew 2012-08-01 10:19
    At least he used temp tables.
  • Sreejith K. 2012-08-01 10:21
    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.


    I agree.
  • John 2012-08-01 10:21
    Tim:
    Kriis:
    Wow... just... wow

    likewise


    My reaction was "Oh god, please no", followed by "no, no, no, no..." as I continued to scroll down.
  • Matyas 2012-08-01 10:22
    Somebody please shoot me now! Reading through this is causing me so much pain...
  • PiisAWheeL 2012-08-01 10:25
    Who has time to write all this shit? PBTL?

    Edit: On second look, I see no ;'s. Is that all 1 statement? Jesus.
  • iToad 2012-08-01 10:26
    String processing with SQL. What could possibly go wrong?
  • Gary 2012-08-01 10:30
    Not a code monkey:
    You can do this in one line in Haskell

    You can do it in one line in a lot of languages. More to the point, you can probably do it in ~1 line of SQL, if you let that line get very long.

    Breaking these up for readability:


    select left(lname, case charindex(' ', lname) when 0 then len(lname) else charindex(' ',lname)-1 end) as part1,
    right(lname, len(lname) - charindex(' ', lname)) as part2,
    lname
    from nametable


    Limiting this to looking at the last four characters would be trivial.
  • Chronomium 2012-08-01 10:34
    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.
  • Zylon 2012-08-01 10:35
    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.

    This is mixing cake batter using another cake.
  • russianyaz 2012-08-01 10:40
    Whaaat?!??!

  • dguthurts 2012-08-01 10:51
    TRWTF is that first name, last name, and initials are not separate fields in the table.

    Gawd. I wonder how this mess would handle Mr. John Smith, Jr. DDS?
  • Valrandir 2012-08-01 10:54
    This can't be real
  • dkf 2012-08-01 10:57
    Zylon:
    This is mixing cake batter using another cake.
    Looks a lot like it's being mixed with a whole bonus bakery.
  • refoveo 2012-08-01 10:59
    My mouse scroll wheel just gave me a blister.
  • Some Jerk 2012-08-01 11:03
    refoveo:
    My mouse scroll wheel just gave me a blister.


    My Krapometer redlined
  • Herwig 2012-08-01 11:07
    Just GREAT!
  • QJo 2012-08-01 11:10
    This one is making my eyeballs bleed.
  • Steve The Cynic 2012-08-01 11:11
    Hey, at least we now know what knives are for.

    Stabbing idiots like this, that's what.
  • ubersoldat 2012-08-01 11:24
    I don't know why but this looks like auto-generated code, it has to be auto-generated code, only an algorithm can be this DUMB!

    From this we can conclude that the surname table column is VARCHAR(54) right? Luckily it was VARCHAR(255).
  • Disguntled DBA 2012-08-01 11:26
    Some of the lesser sins that would merely have his code bounced in code review:

    1) Ordering on insert into a temp table (WHY??)
    2) Casting join columns (Way to use indexes, dumbass)

    In this case, however, I think the correct course of action is job retraining. Preferably something involving paper hats, a fryolator, and a requirement that he wear a badge that reads "TRAINEE" for the first year.
  • Andrew 2012-08-01 11:36
    Other people, when confronted with a problem like this think "Gee, I'll use regex!"

    Oh look, a problem sanely solved.
  • galgorah 2012-08-01 11:38
    String Parsing is really not a good idea in SQL. Its a performance killer. This looks like something that would be better suited in the application code. If a lot of string parsing has to be done in sql, its a candidate for SQLCLR.

    That being said. There is a much better way to do this...
  • Pablo Lerner 2012-08-01 11:38
    I think just the string processing
  • But 2012-08-01 11:40
    Andrew:
    At least he used temp tables.

    That could actually make it worse. If this is SQL Server (which it looks like) then temporary tables are created in tempdb, which means that the script will fail if the account running it doesn't have Create Table permissions in tempdb.
  • Grzechooo 2012-08-01 11:41
    Hmm, I ain't a SQL expert, but dealing with strings is something on app side, not on SQL side, am I right?
  • Some Jerk 2012-08-01 11:44
    Depends on the circumstances. There are better ways to do it on the SQL end as well. Under the circumstances however, given that it only transforms the output, it would definately be better to do this on the app end.
  • dc 2012-08-01 11:52
    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.


    But on the plus side, they passed their typing final with flying colors! 666 words per second?
  • YR 2012-08-01 11:53
    I could claim this is bullshit and the sender faked it... but I doubt he'd go into the trouble of creating this, so what I can say is...

    ... take this expert, and tie him. And bury him. Waist below into an ant's nest. Under a hornet's nest. And kick the hornets. Please.
  • Demon 2012-08-01 11:56
    This isn't a query, it's a war declaration!
  • Some Jerk 2012-08-01 12:01
    Demon:
    This isn't a query, it's a war declaration!


    It is what happens when you attempt to create Artificial Intelligence from Authentic Stupidity.
  • paquetted 2012-08-01 12:05
    One word: OMG!
  • coward 2012-08-01 12:08
    Exactly this
  • ShatteredArm 2012-08-01 12:08
    What an idiot. He should've used table variables.
  • persto 2012-08-01 12:09
    God forbid writing a function that splits strings by space characters (while maybe uniforming space characters first).
  • TK 2012-08-01 12:11
    Kriis:
    Wow... just... wow


    That's all I could say when I was looking at the code. This is an epic WTF
  • Evan 2012-08-01 12:20
    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.)
  • Wow... 2012-08-01 12:27
    I'm no expert either, but I'm speechless...
    By the way, cool name iToad...
  • Ignatz 2012-08-01 12:28
    Any fule no you have to use XML for really enterprisey string manipulation in SQL.

    SELECT XmlName.value('FirstName[1]', 'varchar(30)'),
    XmlName.value('LastName[1]', 'varchar(30)')
    FROM (
    SELECT XmlName = CAST('<FirstName>' + REPLACE(clinician_name, ' ', '</FirstName><LastName>') + '</LastName>' AS XML)
    FROM (
    SELECT clinician_name = 'Bugs Bunny'
    UNION ALL SELECT 'Fred Flintstone'
    UNION ALL SELECT 'Barney Rubble'
    ) names
    ) split

    Let values with anything other than exactly one space character be an exercise for the student.
  • Not at all 2012-08-01 12:42
    Loop unrolling always gives you that critical performance boost.
  • myName 2012-08-01 12:46
    The real WTF is posting all of it without any snipping.
  • Keith 2012-08-01 12:54
    This leads to a delicious vodka and orange infused cake.
  • bob goatse 2012-08-01 12:59
    it hurts. please make it stop.
  • snoofle 2012-08-01 13:10
    Some Jerk:
    Demon:
    This isn't a query, it's a war declaration!


    It is what happens when you attempt to create Artificial Intelligence from Authentic Stupidity.
    Oh Dear Lord: may I use that?
  • Some Jerk 2012-08-01 13:14
    snoofle:
    Some Jerk:
    Demon:
    This isn't a query, it's a war declaration!


    It is what happens when you attempt to create Artificial Intelligence from Authentic Stupidity.
    Oh Dear Lord: may I use that?


    Please do! I am really quite proud of it!
  • Ross 2012-08-01 13:25
    IME, the REAL WTF is that some pointy-haired boss will look upon this code's creator as a genius coder, protect him politically in the organization and appoint him as mentor to any junior SQL developers coming into the department.
  • Some Jerk 2012-08-01 13:27
    Ross:
    IME, the REAL WTF is that some pointy-haired boss will look upon this code's creator as a genius coder, protect him politically in the organization and appoint him as mentor to any junior SQL developers coming into the department.


    Isn't that kind of like keeping the blind, deformed, runt pup from the litter for breeding stock?
  • Infinite Time and Space 2012-08-01 13:27
    dguthurts:
    TRWTF is that first name, last name, and initials are not separate fields in the table.


    Completely agree. But I once had to write something similar to parse names into their parts to PUT them in different fields in the database. But what do you do with names like Billy Bob Thorton and Eddie Van Halen which no system can handle. Or international names where the family name comes first.
  • Cad Delworth 2012-08-01 13:40
    PiisAWheeL:
    Edit: On second look, I see no ;'s. Is that all 1 statement? Jesus.

    Um … in most dialects of SQL, there aren't any line terminator characters (like ;). And yes, I do know they exist in some SQL dialects, but they don't exist not in the ofishul SQL definition; nor do they exist in Microsoft SQL Server.

    And to answer your question, only the abomination that starts SELECT * is one statement: every SELECT statement before the last one is a separate SQL statement within what is probably a stored procedure of some sort.

    So THAT one thing is okay.

    As for the the rest, like the content of the initial bunch of SELECTs … >S I G H<.

  • Tasty 2012-08-01 13:48
    galgorah:
    String Parsing is really not a good idea in SQL. Its a performance killer. This looks like something that would be better suited in the application code. If a lot of string parsing has to be done in sql, its a candidate for SQLCLR.

    That being said. There is a much better way to do this...


    More to the point, Parsed Strings are a better idea in SQL. If the application parses the strings before storing them in the columns, then SQL can index them.

    Databases exist to query and calculate results from data, not process that data.
  • phelmer 2012-08-01 13:53
    This looks less like it was scripted and more like the 'ol "fill down" in Excel, then run through a SQL formatter.

    Maybe the person who came up with that had nobler intentions... "With enough UNIONs, I can bring about world peace."


    CAPTCHA: consequat = fruit that makes you wish you "hadn't"
  • Some Jerk 2012-08-01 13:56
    Tasty:
    galgorah:
    String Parsing is really not a good idea in SQL. Its a performance killer. This looks like something that would be better suited in the application code. If a lot of string parsing has to be done in sql, its a candidate for SQLCLR.

    That being said. There is a much better way to do this...


    More to the point, Parsed Strings are a better idea in SQL. If the application parses the strings before storing them in the columns, then SQL can index them.

    Databases exist to query and calculate results from data, not process that data.


    Yes... but we are talking about someone who didn't know better than to store all three part of the name in seperate fields, dispite virtually every website on the internet seperating them on the presentation layer.

    So...
    WTF : There was a much better way to output parts of a column as seperate columns...
    WTF : The columns should have been seperated to begin with
    WTF : Since we are retrieving the data as is... and leaving intact the present structure of the table, the application would have been the ideal place to seperate them.

    I guess this is 3 WTFs for the price of one.

    CAPTCHA: nulla... ummm... a good name for an explicitly declared global constant who'se value is null? HEY! THAT MAKES 4WTFs ... does it count?
  • Spewin Coffee 2012-08-01 13:58
    THIS is why medical systems are so slow. That combined with the udder disaster that is the MUMPS programming language.
  • Some Jerk 2012-08-01 14:02
    Spewin Coffee:
    THIS is why medical systems are so slow. That combined with the udder disaster that is the MUMPS programming language.


    Sure... but just so long as they continue to use string valued primary keys, they should be okay.
  • tangouniform 2012-08-01 14:17
    This guy is an obvious user of that "little blue pill". That code has to be the result of blurred vision and a 4-hour hard-on.

    CAPTCHA: "praesent" -- that's what he left the maintenance programmers...
  • DCRoss 2012-08-01 14:38
    Sounds like someone has a case of the Getting-Paid-By-The-Line-Of-Code-Days.
  • Disguntled DBA 2012-08-01 15:19
    Cad Delworth:
    PiisAWheeL:
    Edit: On second look, I see no ;'s. Is that all 1 statement? Jesus.

    Um … in most dialects of SQL, there aren't any line terminator characters (like ;). And yes, I do know they exist in some SQL dialects, but they don't exist not in the ofishul SQL definition; nor do they exist in Microsoft SQL Server.



    Actually, the semi-colon has been accepted in SQL Server since at least SQL 2005. In fact, a semi-colon is actually required in some cases (such as after a MERGE statement or immediately prior to a CTE declaration).
  • Zog 2012-08-01 15:34
    I'd say "WTF??!?!!!" but alas, I've seen some of these monster SQL statement too...

    ...my deepest sympathies, I truly feel for you
  • wonder 2012-08-01 15:38
    Does it work ?
  • DoctaJonez 2012-08-01 15:47
    Wow I need new eyes after reading that. He clearly shold have used a cursor and a variety of regexes to improve readability.
  • dogmatic 2012-08-01 16:01
    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.
  • Gil 2012-08-01 16:01
    I can't decide whether this reminds me more of Philip Glass or Andy Kaufman.
  • Blipity 2012-08-01 16:51
    galgorah:
    String Parsing is really not a good idea in SQL. Its a performance killer. This looks like something that would be better suited in the application code. If a lot of string parsing has to be done in sql, its a candidate for SQLCLR.

    That being said. There is a much better way to do this...


    If you think SQLCLR is the solution for this, you probably shouldn't be writing this function either.
  • lrucker 2012-08-01 16:59
    As a double-named person I was going "please no" at "attempts to split a name like 'HARTLEY JR' into two parts."

    Then I saw the code.
  • Blipity 2012-08-01 17:19
    Cad Delworth:
    PiisAWheeL:
    Edit: On second look, I see no ;'s. Is that all 1 statement? Jesus.

    Um … in most dialects of SQL, there aren't any line terminator characters (like ;). And yes, I do know they exist in some SQL dialects, but they don't exist not in the ofishul SQL definition; nor do they exist in Microsoft SQL Server.

    And to answer your question, only the abomination that starts SELECT * is one statement: every SELECT statement before the last one is a separate SQL statement within what is probably a stored procedure of some sort.

    So THAT one thing is okay.

    As for the the rest, like the content of the initial bunch of SELECTs … >S I G H<.



    Eh, the new go forward for all new SQL commands introduced in SQL Server is that the new commands require terminator characters. It's ASNI SQL compliant.

    From MSDN: ";" Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.
  • Troll 2012-08-01 17:38
    I don't see the problem, some people really might have a 50 letter surname, especially if it's hyphenated or (as in some traditions) multiple names....
  • Callin 2012-08-01 17:52
    This is a proud step forward in SQL evolution. Soon we will have 3D games coded in SQL.
  • hikari 2012-08-01 18:00
    dguthurts:
    TRWTF is that first name, last name, and initials are not separate fields in the table.

    Gawd. I wonder how this mess would handle Mr. John Smith, Jr. DDS?


    Assuming everyone's name follows the same form is generally a bad idea. Although I don't think anyone has actually come up with a decent solution.

    A page I am fond of: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

    In other news, fuck Akismet.
  • Mark Wilden 2012-08-01 18:03
    What's a "fryolator", DDBA?
  • Mick 2012-08-01 18:18
    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.
  • Drure 2012-08-01 18:22
    Some Jerk:
    Ross:
    IME, the REAL WTF is that some pointy-haired boss will look upon this code's creator as a genius coder, protect him politically in the organization and appoint him as mentor to any junior SQL developers coming into the department.


    Isn't that kind of like keeping the blind, deformed, runt pup from the litter for breeding stock?
    It is, but this is how empires are built. Competent people are far too big a threat to management. Incompetent people pose no threat and have the added bonus of providing a scapegoat for 'most any issue you'll come across.
  • Remy 2012-08-01 18:23
    phelmer:
    This looks less like it was scripted and more like the 'ol "fill down" in Excel, then run through a SQL formatter.

    Maybe the person who came up with that had nobler intentions... "With enough UNCIORNs, I can bring about world peace."


    CAPTCHA: consequat = fruit that makes you wish you "hadn't"

    FTFY
  • zelmak 2012-08-01 18:42
    Callin:
    This is a proud step forward in SQL evolution. Soon we will have 3D games coded in SQL.


    Or search engines:

    http://www.youtube.com/watch?v=Es9-l1up3r8
  • Ken B. 2012-08-01 19:14
    Not a code monkey:
    You can do this in one line in Haskell
    And even less in APL.
  • Ken B. 2012-08-01 19:17
    dguthurts:
    TRWTF is that first name, last name, and initials are not separate fields in the table.

    Gawd. I wonder how this mess would handle Mr. John Smith, Jr. DDS?
    Probably about as well as the postcard I got some years back, addressed to "John Smith, Mgr. Software Dev.", with the salutation "Dear John Dev.,"
  • Norman Diamond 2012-08-01 19:17
    This is auto generated code. I used to own the auto that generated it. The auto's generator was so worn out, even the replacement generator was worn out. That auto was the real WTF.
  • Norman Diamond 2012-08-01 19:19
    Mick:
    Evan:
    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.
    My Swiss Army Knife doesn't have a fork in it.

    My Linux PC has it though.
  • Gizmokid2005 2012-08-01 19:25
    The fact that he didn't just use a few substring and replace functions to do this is just appalling. I would not be able to live with myself if this was something I wrote...
  • foxyshadis 2012-08-01 20:00
    ubersoldat:
    I don't know why but this looks like auto-generated code, it has to be auto-generated code, only an algorithm can be this DUMB!

    From this we can conclude that the surname table column is VARCHAR(54) right? Luckily it was VARCHAR(255).

    With any luck, we'll find out that the consultant wrote the code to generate the code while he was getting paid to do real work.
  • herby 2012-08-01 21:05
    Norman Diamond:
    Mick:
    Evan:
    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.
    My Swiss Army Knife doesn't have a fork in it.

    My Linux PC has it though.


    So we come to the quote:
    When you come to the fork in the road TAKE IT
  • Evoex 2012-08-01 21:34
    I'm torn between saying "this is amazing" or "this is fake". I can't tell which, but it's definitely either of the two.

    Though I do wonder how much better this was because of the lack of code snippets. Scrolling through it completely just feels more wtf-ish than "[snipped 10.000 lines]".

    I'd say good job, keep it in there fully from now on, Alex! Though I did have to switch of "full text" mode.
  • Evoex 2012-08-01 21:36
    Evoex:
    I'm torn between saying "this is amazing" or "this is fake". I can't tell which, but it's definitely either of the two.

    Though I do wonder how much better this was because of the lack of code snippets. Scrolling through it completely just feels more wtf-ish than "[snipped 10.000 lines]".

    I'd say good job, keep it in there fully from now on, Alex! Though I did have to switch of "full text" mode.


    "code snippets"... I meant: "removed code". "snipped code"? Ahh, whatever it's called.
  • Coyne 2012-08-01 22:25
    iToad:
    String processing with SQL. What could possibly go wrong?


    Not even one teensy little bitty thing!

    SQL annoys me because it pretends to offer stuff you could use to do this, but it provides neither the condition nor the scope that would actually allow you to do it. In theory, for example, you should be able to to use LOCATE() to do this, but you wind up with this monstrosity (to get last name from a "LAST, FIRST" field):


    SUBSTR(N,1,CASE WHEN LOCATE(', ',N) > 0 THEN LOCATE(', ',N) ELSE 31 END - 1)


    So, to split a field into last name and first name, I have to reference the same LOCATE a total of 4 times. And if I were to need to split one of the result fields again; well, I shudder even to think. If I have nested query tables, like in DB2, it can be made to work, but it is so clumsy.

    To pretend that this is programming is to pretend that torture is a sunny day at the beach.


    Addendum (2012-08-01 22:50):
    I decided this needed more clarification.

    To make SQL look like it is programmable, the designers of the "language" emulate functions from other languages. The LOCATE function, for example, is a take-off from POS in Basic; and SUBSTR, likewise.

    Unfortunately, SQL is not BASIC, and these functions are not suitable for use in a set language. So they don't fit the scope of the SQL language, because its scope is much more restrictive than the scope of a true programming language.

    Even if they insisted on emulating such functions exactly, things would be made better if they bothered to anticipate the conditions you are likely to encounter when processing data, and allowed the functions to be more tolerant of those conditions. They won't do that either.

    The result is that string processing in SQL is often an exercise in pure pain, unless of the data in that column conforms exactly to a specification. In this case, for example, the above can be greatly simplified if I can guarantee that every row contains a ', '. But, of course, no one wants to be bothered to keep data to a specification, including database designers.

    There are a lot of things that could be done to ease this, if only they thought of SQL as a language in its own right, and stopped emulating everything else in its most restrictive form. As an example of this, suppose that there were two changes to the functions: (1) LOCATE returns null if the target string is not found; and (2) SUBSTRING accepts parameters outside the bounds of the string at the "edges". Then, a last-name first-name split of this type could be written:


    SELECT
    SUBSTR(N,1,COALESCE(LOCATE(', ',N),31) - 1) AS LAST
    ,SUBSTR(N,COALESCE(LOCATE(', ',N),29) + 2) AS FIRST


    Now, gee, wouldn't that be an improvement?

    Even better would be if they actually thought about the problems that query designers are likely to face, so that if we had CSV data in a column, we could do something like this:


    SELECT
    SPLIT(COL,1,',','"') AS FIRST
    , SPLIT(COL,2,',','"') AS SECOND
    , SPLIT(COL,3,',','"') AS THIRD
    , SPLIT(COL,4,',','"') AS FOURTH


    And then of course, we have:


    SELECT
    SPLIT(N,1,', ') AS LAST_NAME
    , SPLIT(N,2,', ') AS FIRST_NAME


    (SPLIT parameters: 1-input column; 2-desired segment; 3-delimiter between the segments; 4-text qualifier as in CSV.)

    And wouldn't that be easy?

    So it would be so much better if they thought about the scope within which SQL expressions must operate and the conditions likely to be encountered in the data.



    Addendum (2012-08-01 22:55):
    And, by the way, another annoying thing.

    In DB2, I can cast a character string to a number:


    SELECT INTEGER('12345') AS MY_NUM


    ...and so, of course, they provide no/nada/zilch/zip means to determine if the string is numeric beforehand.

    So if I run this on a table column and hit a row that contains "XYZZY" in that column; well, too bad, dies.

    It would be much better if there were an express function like CONVERT_INTEGER() that simply returned NULL if the text was not a valid number. But who would think of anything like that?
  • Silverhill 2012-08-01 23:25
    Ken B.:
    dguthurts:
    I wonder how this mess would handle Mr. John Smith, Jr. DDS?
    Probably about as well as the postcard I got some years back, addressed to "John Smith, Mgr. Software Dev.", with the salutation "Dear John Dev.,"
    Similarly, a woman at my company (call her "Mary Ann Fulton") received some admail addressed to "Mr. Mary Ann". The salutation was "Dear Mr. Ann:" ... she posted it on the bulletin board for general laffs.
  • Boager 2012-08-02 00:03
    Silverhill:
    Ken B.:
    dguthurts:
    I wonder how this mess would handle Mr. John Smith, Jr. DDS?
    Probably about as well as the postcard I got some years back, addressed to "John Smith, Mgr. Software Dev.", with the salutation "Dear John Dev.,"
    Similarly, a woman at my company (call her "Mary Ann Fulton") received some admail addressed to "Mr. Mary Ann". The salutation was "Dear Mr. Ann:" ... she posted it on the bulletin board for general laffs.

    Does she write cookbooks....?
  • Dirk 2012-08-02 00:36
    To a mere mortal, this seems like a WTF. However, to an SQL "guru"...

    *trails off into thoughts of the space-time continuum*
  • Meep 2012-08-02 01:11
    Zylon:
    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.

    This is mixing cake batter using another cake.


    This is mixing cake batter by pouring the mix, water and eggs into the bowl, then scooping it out into 165 separate portions into 165 tupperware containers, shuffling them around the counter, and then pushing them back into the bowl.
  • Meep 2012-08-02 01:26
    Coyne:
    The result is that string processing in SQL is often an exercise in pure pain, unless of the data in that column conforms exactly to a specification. In this case, for example, the above can be greatly simplified if I can guarantee that every row contains a ', '. But, of course, no one wants to be bothered to keep data to a specification, including database designers.


    You're not the only ones who don't get this, that's why the SQL standard allows any number of procedural languages to be embedded to work around your deficient design.

    SQL is a (roughly) relational language. If you're manipulating strings in it, you're Doing It Wrong. See <a href="http://www.dbdebunk.com/page/page/629796.htm">this paper</a> ($$ but worth it) and the first few chapters of CJ Date's Introduction to Database Systems.

    But the bottom line is if you have "Mr. Smith" in your database and need access to "Mr." or "Smith", you should have split them into fields *before* they came into the database. The beauty of the relational schema is that, when properly normalized, all the informational complexity is clearly laid out: your database is exactly as complex as that schema looks. If you remove stuff or cheat to make it pretty, you are deluding yourself. If you do stupid tricks like entity attribute value, you have only hidden the complexity, but it's still there and will simply migrate to your app, usually in the form of a buggy rewrite of DBMS functionality.
  • Wim 2012-08-02 02:51
    PiisAWheeL:

    Edit: On second look, I see no ;'s. Is that all 1 statement? Jesus.

    It is probably T-SQL coming from SQL Server. Not sure if that is using ; . It is not Oracle which uses a ; as a delimiter of multiple commands.
  • Matt 2012-08-02 04:08
    Wow how efficient is that
  • Sherlock Holmes 2012-08-02 04:48
    The use of 'JR Hartley' as an example name indicates the submitter is at least mid-thirties and grew up in UK.
  • Andrew 2012-08-02 05:55
    But:
    Andrew:
    At least he used temp tables.

    That could actually make it worse. If this is SQL Server (which it looks like) then temporary tables are created in tempdb, which means that the script will fail if the account running it doesn't have Create Table permissions in tempdb.


    You don't need extra-special permissions to create temp tables in MSSQL (tested it with vanilla sql authentication account).

    Also: which is worse? Creating temp tables or creating actual tables (and dropping them later) which needs *more* rights?

    Also: I was being ironic. Nothing can possibly excuse a script like this!
  • Nagesh 2012-08-02 07:19
    In our dev shop in Hyderabad this is know as NoSQL solution. Client look at SQL code and say "no" many times.
  • Cujo 2012-08-02 07:42
    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!"
  • biziclop 2012-08-02 07:53
    This post calls for a "bloody hell" button.
  • havokk 2012-08-02 07:54
    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 2012-08-02 08:07
    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 2012-08-02 08:16
    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 2012-08-02 08:41
    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 2012-08-02 08:41
    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 2012-08-02 08:41
    Damn Parkinson.
  • Ryan 2012-08-02 09:23
    iToad:
    String processing with SQL. What could possibly go wrong?

    Amen
  • golddog 2012-08-02 09:57
    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 2012-08-02 12:38
    I never comment here, but seeing this code made a void in my soul and I had to share it.
  • lietu 2012-08-02 13:23
    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 2012-08-02 14:39
    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 2012-08-02 17:20
    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 2012-08-02 18:09
    I think this SQL can be optimized.
  • Mango Cream 2012-08-02 21:39
    This is why I hate string manipulations in SQL. (*A*)/
  • TallMan 2012-08-03 01:44
    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 2012-08-03 07:39
    String processing with SQL. It already went wrong.
  • Ziplodocus 2012-08-03 10:38
    If that captcha is true, what is the definition of a cumquat?!
  • mike 2012-08-03 16:02
    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 2012-08-03 18:08
    It goes on forever — and — oh my God — it's full of stars!
  • Andrew 2012-08-03 20:29
    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 2012-08-04 09:07
    I have never worked with SQL but I get a slight suspicion that this can be done more efficiently.
  • David 2012-08-05 05:20
    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 2012-08-06 05:28
    Pure and simple design issue. Break out the individual columns in the target table and do the splits in the ETL code.
  • Sander 2012-08-07 04:58
    That seriously looks like some awful generated SQL. Nobody could've typed that manually.
  • roselan 2012-08-07 13:52
    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 2012-08-08 17:18
    Wow. He really is a guru. You should study hard and learn from him.
  • wkmanire 2012-08-09 02:51
    What...How.... dammit! Come on! At what point do you stop and consider that you may be doing it wrong!?
  • Efficiency Expert 2012-08-09 14:25
    So this is what happens when SQL does the splits.

    This is why I always recommend stretching.
  • Jan 2012-08-10 04:07
    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 2012-08-10 12:06
    I almost just screamed out loud at work while scrolling through that.
  • Brian 2012-08-13 07:07
    Finally a robust solution to splitting a string in T-SQL!
  • PITA 2012-08-13 15:11
    Other than all of the carriage returns, I don't see any problems.
  • TJ Powell 2012-08-15 04:58
    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 2012-08-15 05:00
    The answer to when you know is after 1.5 "screen" pages. If it's long than that, it's wrong.
  • Conrad 2012-08-22 13:40
    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 2012-09-01 12:56
    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 2012-09-01 13:14
    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 2012-09-20 20:22
    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 2012-09-20 20:30
    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