• bob goatse (unregistered)

    it hurts. please make it stop.

  • (cs) in reply to Some Jerk
    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 (unregistered) in reply to snoofle
    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 (unregistered)

    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 (unregistered) in reply to Ross
    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 (unregistered) in reply to dguthurts
    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.

  • (cs) in reply to PiisAWheeL
    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 (unregistered) in reply to galgorah
    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 (unregistered)

    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 (unregistered) in reply to Tasty
    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 (unregistered)

    THIS is why medical systems are so slow. That combined with the udder disaster that is the MUMPS programming language.

  • Some Jerk (unregistered) in reply to Spewin Coffee
    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 (unregistered)

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

  • (cs)

    Sounds like someone has a case of the Getting-Paid-By-The-Line-Of-Code-Days.

  • Disguntled DBA (unregistered) in reply to Cad Delworth
    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 (unregistered)

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

    Does it work ?

  • (cs)

    Wow I need new eyes after reading that. He clearly shold have used a cursor and a variety of regexes to improve readability.

  • dogmatic (unregistered)

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

    I can't decide whether this reminds me more of Philip Glass or Andy Kaufman.

  • Blipity (unregistered) in reply to galgorah
    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.

  • (cs) in reply to John

    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 (unregistered) in reply to Cad Delworth
    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 (unregistered)

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

  • (cs)

    This is a proud step forward in SQL evolution. Soon we will have 3D games coded in SQL.

  • (cs) in reply to dguthurts
    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 (unregistered) in reply to Disguntled DBA

    What's a "fryolator", DDBA?

  • Mick (unregistered) in reply to Evan
    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 (unregistered) in reply to Some Jerk
    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 (unregistered) in reply to phelmer
    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

  • (cs) in reply to Callin
    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. (unregistered) in reply to Not a code monkey
    Not a code monkey:
    You can do this in one line in Haskell
    And even less in APL.
  • Ken B. (unregistered) in reply to dguthurts
    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 (unregistered)

    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 (unregistered) in reply to Mick
    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 (unregistered)

    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 (unregistered) in reply to ubersoldat
    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.

  • (cs) in reply to Norman Diamond
    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 (unregistered)

    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 (unregistered) in reply to Evoex
    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.

  • (cs) in reply to iToad
    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?

  • (cs) in reply to Ken B.
    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 (unregistered) in reply to Silverhill
    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 (unregistered)

    To a mere mortal, this seems like a WTF. However, to an SQL "guru"...

    trails off into thoughts of the space-time continuum

  • Meep (unregistered) in reply to Zylon
    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 (unregistered) in reply to Coyne
    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 this paper ($$ 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 (unregistered) in reply to PiisAWheeL
    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 (unregistered)

    Wow how efficient is that

  • Sherlock Holmes (unregistered)

    The use of 'JR Hartley' as an example name indicates the submitter is at least mid-thirties and grew up in UK.

  • Andrew (unregistered) in reply to But
    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!

Leave a comment on “SQL Splits”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article