Comment On SQL Splits

"I should probably start by saying that I am not a SQL expert," Paul wrote. "Sure, I've written my share of SELECT statements, plenty of UPDATE statements, and even a few ALTER statements, but beyond that... nada." [expand full text]
« PrevPage 1 | Page 2 | Page 3Next »

Re: SQL Splits

2012-08-02 07:19 • by Nagesh (unregistered)
In our dev shop in Hyderabad this is know as NoSQL solution. Client look at SQL code and say "no" many times.

Re: SQL Splits

2012-08-02 07:42 • by Cujo (unregistered)
386186 in reply to 386087
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!"

Re: SQL Splits

2012-08-02 07:53 • by biziclop
This post calls for a "bloody hell" button.

Re: SQL Splits

2012-08-02 07:54 • by havokk
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.

Re: SQL Splits

2012-08-02 08:07 • by 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.

Re: SQL Splits

2012-08-02 08:16 • by 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.


Re: SQL Splits

2012-08-02 08:41 • by 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.

Re: SQL Splits

2012-08-02 08:41 • by 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.

Re: SQL Splits

2012-08-02 08:41 • by urgh (unregistered)
Damn Parkinson.

Re: SQL Splits

2012-08-02 09:23 • by Ryan (unregistered)
386200 in reply to 386081
iToad:
String processing with SQL. What could possibly go wrong?

Amen

Re: SQL Splits

2012-08-02 09:57 • by golddog (unregistered)
386216 in reply to 386149
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.

Re: SQL Splits

2012-08-02 12:38 • by Indeedly (unregistered)
386239 in reply to 386081
I never comment here, but seeing this code made a void in my soul and I had to share it.

Re: SQL Splits

2012-08-02 13:23 • by 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;

?

Re: SQL Splits

2012-08-02 14:39 • by 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!

Re: SQL Splits

2012-08-02 17:20 • by Paul Neumann (unregistered)
386262 in reply to 386239
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.

Re: SQL Splits

2012-08-02 18:09 • by Joe (unregistered)
I think this SQL can be optimized.

Re: SQL Splits

2012-08-02 21:39 • by Mango Cream (unregistered)
This is why I hate string manipulations in SQL. (*A*)/

Re: SQL Splits

2012-08-03 01:44 • by 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!

Re: SQL Splits

2012-08-03 07:39 • by Quidam (unregistered)
386287 in reply to 386081
String processing with SQL. It already went wrong.

Re: SQL Splits

2012-08-03 10:38 • by Ziplodocus (unregistered)
386323 in reply to 386131
If that captcha is true, what is the definition of a cumquat?!

Re: SQL Splits

2012-08-03 16:02 • by 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?

Re: SQL Splits

2012-08-03 18:08 • by Patrick (unregistered)
It goes on forever — and — oh my God — it's full of stars!

Re: SQL Splits

2012-08-03 20:29 • by 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.

Re: SQL Splits

2012-08-04 09:07 • by Philipp (unregistered)
I have never worked with SQL but I get a slight suspicion that this can be done more efficiently.

Re: SQL Splits

2012-08-05 05:20 • by David (unregistered)
386405 in reply to 386157
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.

Re: SQL Splits

2012-08-06 05:28 • by jtf (unregistered)
Pure and simple design issue. Break out the individual columns in the target table and do the splits in the ETL code.

Re: SQL Splits

2012-08-07 04:58 • by Sander (unregistered)
That seriously looks like some awful generated SQL. Nobody could've typed that manually.

Re: SQL Splits

2012-08-07 13:52 • by 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.

Re: SQL Splits

2012-08-08 17:18 • by lonetaco (unregistered)
Wow. He really is a guru. You should study hard and learn from him.

Re: SQL Splits

2012-08-09 02:51 • by wkmanire (unregistered)
What...How.... dammit! Come on! At what point do you stop and consider that you may be doing it wrong!?

Re: SQL Splits

2012-08-09 14:25 • by Efficiency Expert (unregistered)
So this is what happens when SQL does the splits.

This is why I always recommend stretching.

Re: SQL Splits

2012-08-10 04:07 • by 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!"

Re: SQL Splits

2012-08-10 12:06 • by Dan (unregistered)
I almost just screamed out loud at work while scrolling through that.

Re: SQL Splits

2012-08-13 07:07 • by Brian (unregistered)
Finally a robust solution to splitting a string in T-SQL!

Re: SQL Splits

2012-08-13 15:11 • by PITA (unregistered)
Other than all of the carriage returns, I don't see any problems.

Re: SQL Splits

2012-08-15 04:58 • by TJ Powell (unregistered)
387266 in reply to 386081
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

Re: SQL Splits

2012-08-15 05:00 • by TJ Powell (unregistered)
387267 in reply to 387266
The answer to when you know is after 1.5 "screen" pages. If it's long than that, it's wrong.

Re: SQL Splits

2012-08-22 13:40 • by Conrad (unregistered)
387920 in reply to 386190
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

Re: SQL Splits

2012-09-01 12:56 • by JoI hope this is a jopkee Celko (unregistered)
388845 in reply to 386081
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.

Re: SQL Splits

2012-09-01 13:14 • by Joe Celko (unregistered)
388846 in reply to 386382
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.

Re: SQL Splits

2012-09-20 20:22 • by barfo rama (unregistered)
390556 in reply to 386074
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

Re: SQL Splits

2012-09-20 20:30 • by barfo rama (unregistered)
390557 in reply to 386160
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
« PrevPage 1 | Page 2 | Page 3Next »

Add Comment