• (nodebb)

    The reason Oracle is hated so much, are "developers" like these. And the pricing. The reasons Oracle is hated so much, are "developers" like these, and the pricing. And the tooling. Amongst the reasons Oracle is hated so much, are "developers" like these, the pricing, and the tooling.

    Nobody expects code like this.

  • (nodebb) in reply to nerd4sale

    Nobody expects code like this.

    So this code is the Spanish Inquisition?

  • (nodebb) in reply to nerd4sale

    ROFL

  • Sauron (unregistered) in reply to Steve_The_Cynic

    I think I would actually prefer the Spanish Inquisition than maintaining that codebase.

    Jokes aside, good luck to Mateus!

  • Hal (unregistered)

    There are two rules of data, that everyone feels compelled to break at some point because it it seems like it would make the immediate problem so easy, but will almost unfailingly lead future problems that are far more difficult t solve.

    1. If you think you should or can just use a natural key, you are almost certainly wrong.
    2. A multi-valued field/attribute will be okay here because I know these two datums always have a perfect and fixed 1:1 relationship, your certainty about that relationship will be proven incorrect if you are around long enough.

    With probably half my career in information systems now in the rear-view mirror if there were two things I tell new people what your information theory text said on these subjects is entirely correct, just treat it as gospel, take it as an article of faith it will make your life better, those would be it.

  • Zeh (unregistered)

    The code really sucks but it is not about city names, it is actually about the states in Brazil. Some of our state capitals share the same name as their states like São Paulo and Rio de Janeiro, for example, adding to the confusion.

    What is even worse, Brazil only has 26 states plus the capital, which is kind of treated as a state. This means this code has some items that should be impossible as it has 30 replaces.

  • Tim (unregistered)

    Unfortunately if you want to do this directly in an SQL SELECT, nested REPLACE may be your only option. If it's Oracle though, DECODE would be rather more readable

  • Richard Brantley (unregistered) in reply to Hal

    The most dangerous words in the English language are "this time, it's different."

  • Mr Bits (unregistered) in reply to Zeh

    So not only is it horribly implemented, it is horribly implemented with incorrect data. It's a WTWTF. Nice.

  • Kleyguerth (github) in reply to Zeh

    EX and XX are not states, RN is replaced twice, that explains the 30 replaces

  • Berry (unregistered) in reply to nerd4sale

    Or the ruthless inefficiency.

  • R Samuel Klatchko (unregistered)

    And they entries will now all have a trailing space.

  • (nodebb) in reply to Hal

    if there were two things I tell new people what your information theory text said on these subjects is entirely correct, just treat it as gospel, take it as an article of faith it will make your life better, those would be it.

    The flaw in your reasoning is in assuming that the programmer who wrote the code and the sucker who has to maintain it are the same person.

  • (nodebb)

    String processing in SQL sucks. Before REGEXP_REPLACE was added, this was the way to do multiple replacements.

  • Fizzlecist (unregistered) in reply to Richard Brantley

    Closely followed by "We'll fix it later"

  • Fizzlecist (unregistered) in reply to AGlezB

    ROFL (YSST) ;-)

  • Sou Eu (unregistered) in reply to Zeh

    I came here to say the same thing; those abbreviations are for the states. TRWTF is storing the city and state in the same database field. If the state were its own field, it would be so much easier to select all the cities within a given state. I may be wrong, but I think "EX" might stand for exemplo (Example), "DF" for the "Distrito Federal" (Federal District / Brasilia), and "XX" for unknown / not available.

    In a weird twist of fate, I think a REGEX to remove the state abbreviation might be the solution easiest to read.

  • markm (unregistered)

    Comparing the hard-coded list to this,

    https://en.wikipedia.org/wiki/Federative_units_of_Brazil#List

    the count in the program is 30 instead of 27 because "XX" and "EX" are not Federative Units, and "RN" is included twice. I suspect the two codes containing "X" have some sort of special meaning.

  • LZ79LRU (unregistered) in reply to Barry Margolin

    Can't he use SELECT LEFT and CHARINDEX together to fetch everything before '('? It's even cleaner than REGEX and IIRC existed way before REGEXP_REPLACE was added.

  • Officer Johnny Holzkopf (unregistered) in reply to LZ79LRU

    How about STRING_SPLIT at the position of '(' determined by CHARINDEX? Not quite most optimum, but less pessimum... four! Four biggest SQL manglers!

  • Jeremy (unregistered)

    Apparently the table definitions are sacred. Create column 'state_code' and fill it with the two char codes . Then you can isolate the name from the code and combine it in all the places where you want to see it together.

    How many time are values combined in a column where it should idealy be 2 or more. Does oracle charge by the number of columns now?

  • Geomancer (unregistered) in reply to R Samuel Klatchko

    Not zo, that's what the RTRIM is for.

Leave a comment on “Replacing a City”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article