• JavaNut (unregistered)

    This is not a WTF. This is not even bad SQL. This is just how character translations were done in most SQL DBs. There was no more elegant way to do it. I think now most of them do have built-in translate functions, but this may be code from before that time.

  • SQLDBA (unregistered) in reply to JavaNut
    JavaNut:
    This is not a WTF. This is not even bad SQL. This is just how character translations were done in most SQL DBs. There was no more elegant way to do it. I think now most of them do have built-in translate functions, but this may be code from before that time.

    This is exactly the kind of response that I've come to expect from Java developers these days...I really wish app dev's would stop trying to be database dev's. JavaNut, just stick with Hibernate, or some other n-tier solution, and everyone will be a lot happier when you're done.

  • JavaNut (unregistered)

    Well, yeah, I do in fact use Hibernate (or EJB3 these days). Look, there was a time when this nested-replace idiom, ugly as it is, was the only way to do certain character translations for some common databases, like MySQL I think. Isn't that right? Back in MySQL 3 or whatever they didn't have all the nice functions they now have so if you wanted to do something like that, what other options were there? You could do it at the application level, by getting the field, doing stuff to it, and then storing it, or you could do it with a mess of SQL like what we see here. I'm just making the point that, in the earlier days of some of the SQL DBs, some ugly code like this was par for the course. This isn't a WTF, this is just used to be the only practical way to achieve certain things, AFAIK.

    These days I hardly do anything with SQL. EJB3 takes care of all that for me. It creates the tables, stores, fetches, etc, and I basically never write SQL anymore. Just like I never write assembly anymore either.

  • JavaNut (unregistered) in reply to SQLDBA

    To put it another way, how would you do that, under MySQL 3 or some of the older DBs? I know you could do it with an application (ie, outside of SQL). How would you do it inside of SQL? What's the non-WTF way to do this, that would have been possible a few years ago?

    I'm saying "a few years ago" because I'm guessing that this is older code that just hasn't been changed.

  • Nicolas V. (unregistered)

    I would loop field names then loop i from 1 to 31 and do replace(@fieldname,chr[@i],null) but a regex would be a lot better.

    This looks like T-SQL, there is no built-in regex but you can make one of use an assembly that does that.

    ANYWAY cleaning up PRIOR to inserting would be great.

    Plus they replace characters that cannot be entered manually through a keyboard.

  • JavaNut (unregistered) in reply to Nicolas V.

    I'm not enough of an SQLer to be able to identify that dialect, but I know that MySQL didn't have loops or procedures until MySQL 5, which is recent enough that many sites aren't even using it yet, and that's my point. On a MySQL 3 system, is there any more elegant way to do this? And I don't think any of these DBs had built-in regexp until recently; I'm not even sure if they all have it yet. I still don't think this is a WTF because it is very likely that it was the only realistic way to do what it does at the time it was written.

    Obviously you are correct that it should be cleaned up prior to being saved. It sounds like this may be a situation where some other piece of code was the real WTF and someone did a quick and efficient way of fixing that mess, and what we're seeing is that quick, efficient, real-world solution. I still don't think this code counts as a WTF. Not saying that I would write that today, but for solving the problem that it solves, at the time it was needed, I'm guessing that was the best way to do that.

  • cklam (unregistered) in reply to diaphanein
    diaphanein:
    Suraj:
    Well AFAIK there is no better way of doing character substitution in SQL. Unless you create a table & perform lookup from that table using a stored procedure.

    I agree. Thing I cannot figure out is that this person seems to be replacing all control characters between 1 and 31, inclusive, w/ nulls. My guess is that somewhere, in either the translate or filter functions, these nulls are removed. I understand the desire to eliminate the control chars...but to do it in sql? What a pain in the arse.

    Isn't there any for-next-loop in SQL. At least in Oracle PL/SQL I remember something like that ....

  • JavaNut (unregistered)

    To know whether or not this is a WTF you would need to know which DB it's running on. I know that MySQL prior to version 5 had no stored procedures, no loops, and was lacking in a bunch of other things. I know that I personally have used exactly this same idiom of nested replace statements a few years ago. I can't remember if it was on MySQL or Postgres, but I do remember that I looked for any other way to do it and I couldn't find any. There were no regexps, no translate() functions, nothing except a big ugly nested statement like that. I still submit that this isn't a WTF, but rather a somewhat ugly idiom that was the just the way you do this kind of thing on systems that lacked some more modern conveniences that we now take for granted.

  • Thomas (unregistered)

    IMO, the WTF here is that the developer did not switch tools when the query got hairy. Yes, this is the way you had to do it IF you were going to do with traditional SQL that did not have user-defined functions. You could do it a bit cleaner using stored procedures but that ignores the glaring issue: SQL isn’t the tool for this job. Once I saw that it required more than about five replacements, I would have done it in a different language/tool that connected to the database.

  • deroby (unregistered) in reply to Nicolas V.

    Nicolas V.: Whatever it is, it MOST DEFINITELY isn't T-SQL.

    ** 'I would loop...' => it would be MUCH, MUCH SLOWER => it would create a much bigger transaction (32 x 32 small updates is A MUCH BIGGER resource hog compared to just 1 big update) => it would require some in-depth knowledge regarding the meta-data stuff (or you would need to hardcode the names of the fields, which would complicate things again and in the end you'll end up with a nice looking (big) program instead of a repetitive (big) script, nicer to look at, but hardly any gain imho)

    ** 'but you can make one of use an assembly that does that.' => Yes, you can do that in MSSQL2005. However, you'll find that most real-world situations are still running MSSQL2k, and who's saying the script isn't much older than that ?

    ** ANYWAY cleaning up PRIOR to inserting would be great.

    That's true indeed and to me also it makes little sense to find this in an UPDATE. However, maybe the guy had to use BCP to load the data into his destination table, not much possibilities there. In that case the poster might find himself lucky he didn't find a remark in the 'conversion scenario' that says "Open data0032.csv in Notepad, then replace all control characters with blanks"... would have worked too, and I'm sure there's plenty of sites where you'll find exactly that kind of stuff, and those are much more WTF-worthy IMHO.

    Actually, I agree mostly with what JaveNut says. There is way too little context here to 'honestly' point the finger at the original author here. Finding "not perfect code" is easy, plenty of that around. But posting it online on WTF simply because you NOW can do better, no, that's not fair.

  • Flecky (unregistered) in reply to treypole

    I agree completely. Why this kind of string manipulation is being done in the database layer is beyond me. Surely this is a perfect example of something that should NOT be deferred to the database layer, but instead the layer above (presumably a DAL component somewhere) should be doing this kind of work. That way, you can ensure "clean" data in your database at all times.

    Mind you, the fact that this is an UPDATE against a complete "set" of data rows indicates that a bigger WTF exists in that they're allowing garbage (control chars) into the database in the first place!

  • JavaNut (unregistered)

    So... it looks like others have agreed with me, this snip of code might not be a WTF because it might be the only or the best way to achieve this on many widely-used not-quite-current-generation DBs.

    As for "there shouldn't be control chars in the DB, the data should be cleaned by the application layer", I can't argue with that, but the fact is, most real-world web apps (esp. in PHP) have SQL injection risks and do end up with crap in the DB. That's not good but every real-world PHP app I've ever looked at has code like mysql_query("update customer set name = " . trim($name) . " where ..."). That is the real WTF but you know what, if TheDailyWTF posted snips like once a week people would find it BORING. mysql_query("update customer set name = " . trim($name) (or its equivalent in other languages) is about the biggest WTF in common use but it's not funny anymore.

  • David Cameron (unregistered) in reply to Jeff S

    Only if ANSI nulls are turned on

  • cheap jerseys (unregistered)

    cheap NFL Jersey Lewis says the Magic knew they could beat the Cavaliers in the Eastern Conference Finals when the the two teams cheap kobe jersey met for Game 2. cheap Paul Smith Shoes It took a miracle shot from LeBron James to beat Orlando cheap nfl jerseys cheap nfl jerseys Bucks are looking very closely at every point guard available in the draft would make you cheap g star think Sessions replica jerseys walk, but it's far replica NFL jerseys, NHL jerseys too early to know. In truth, the Bucks nfl jerseysthemselves haven't f teams are starting to think about what cheap diesel Villanueva would cheap football jerseys look like in their nba jerseysuniforms. One of them might be the Cleveland Cavaliers. cheap NFL Jersey mlb jerseys cheap nfl jerseys James' no-show after Game 6, we're finally getting down to business. Dwight Howard nfl jerseys and Kobe Bryant will lace them up o decide cheap uggs the best team in cheap north face jacket the NBA. The Magic swept the season cheap jerseysseries from the Lakers, one of that feat (Charlotte was the other) cheap nhl jersey Raptors are they'd cheap tommy bahama also like to another selection there. With teams like L.A., Chicago, and Minnesota cheap nhl jersey selling that area, they're replica NFL jerseys, NHL jerseys likely to find cheap hockey jerseys someone they can work with.cheap nba jersey Portland, Houston failed to shifts lebron james jersey back to Houston for Game 6 on Thursday as the Rockets scurry to make Jordan jerseys adjustments cheap nhl jerseys of the Blazers cheap ugg boots Grizzlies, and it's been mentioned that that pick could be had as well. Most teamsbrady jersey looking to buy in cheap owens jersey are looking to buy into cheap ugg boots the late first/early second round. kobe jerseys Memphis has cheap baseball jersey picks potentially available cheap baseball jerseys in both those areas.cheap mlb jerseys Timberwolves use all three of their first round picks this year (6-18-28), and while it's all but certain they'll use cheap true religion jeans their #6 pick and probably the #18, replica jerseys that #28 could absolutely be had. For teams looking to buy into that part of the draft (see below), the Wolves cheap nhl jerseys

  • cheap jerseys (unregistered)

    cheap NFL Jersey Lewis says the Magic knew they could beat the Cavaliers in the Eastern Conference Finals cheap mlb jerseys when the the two teams cheap kobe jersey met for Game 2. cheap Paul Smith Shoes It took a miracle shot from LeBron James to beat Orlando cheap nfl jerseys cheap nfl jerseys Bucks are looking very closely at every point guard available in the draft would make you cheap g star think Sessions replica jerseys walk, but it's far replica NFL jerseys, NHL jerseys too early to know. In truth, the Bucks nfl jerseysthemselves haven't f teams are starting to think about what cheap diesel Villanueva would cheap football jerseys look like in their nba jerseysuniforms. One of them might be the cheap baseball jerseys Cleveland Cavaliers. cheap NFL Jersey mlb jerseys cheap nfl jerseys James' no-show after Game 6, we're finally getting down to business. Dwight Howard nfl jerseys and Kobe Bryant will lace them up o decide cheap uggs the best team in cheap north face jacket the NBA. The Magic swept the season cheap jerseysseries from the Lakers, one of that feat (Charlotte was the other) cheap nhl jersey Raptors are they'd cheap tommy bahama also like to another selection there. With teams like L.A., Chicago, and Minnesota cheap nhl jersey selling that area, they're replica NFL jerseys, NHL jerseys likely to find cheap hockey jerseys someone they can work with.cheap nba jersey Portland, Houston failed to shifts lebron james jersey back to Houston for Game 6 on Thursday as the Rockets scurry to make Jordan jerseys adjustments cheap nhl jerseys of the Blazers cheap ugg boots Grizzlies, and it's been cheap nba jerseys mentioned that that cheap nfl jerseys pick could be had as well. Most teamsbrady jersey looking to buy in cheap owens jersey are looking to buy into cheap ugg boots the late first/early second round. kobe jerseys Memphis has cheap baseball jersey picks potentially available cheap baseball jerseys in both those areas.cheap mlb jerseys Timberwolves use all three of their first round picks this year (6-18-28), and while it's all but certain they'll use cheap true religion jeans their #6 pick and probably the #18, replica jerseys that #28 could absolutely be had. For teamscheap nhl jerseys looking to buy into cheap nhl jerseys that part of the draft (see below), the Wolves cheap nhl jerseys

  • me (unregistered) in reply to deroby

    Exactly, its about time the elitist crap was dropped boys.

Leave a comment on “The Data Cleanup”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article