Thursday, July 30, 2009

munging numbers and strings in Oracle

An item of current interest to me is a compound number. Sometimes I see it in the database as one big number: 12345678901234 and at other times I see it in the database as two shorter numbers: 1234567890 and 1234. So how can I go back and forth? There are a few tricks I've learned to keep up with it:

  1. Always make sure you know which data type the item you want is being stored as. Sometimes this ID is stored as a string, sometimes as a number. Figuring this out depends on the tools you use, but make sure you know what you're working with - you can usually use DESC to find out if you're just sitting at a SQL prompt. Also, I'm assuming in these examples that I can trust my data to be entered correctly (e.g. no one accidentally put a letter in somewhere), which is probably a stretch in real life but will work in my particular case.

  2. When it is two numeric fields and I need one string field, I can format and concatenate the two numbers into a big string:
    CONCAT(TO_CHAR(first_number, 'FM0000000000'),TO_CHAR(second_number, 'FM0000'))
    It took some looking things up to figure out that adding the 'FM' at the beginning of the format string would keep Oracle from leaving a space at the beginning for the +/- sign, which was key to actually matching the string.

  3. When it is one big string field and I need two numbers, I can split them:

    SELECT whatever FROM you_know_what WHERE TO_NUMBER(SUBSTR(big_string_field, 1, 10)) = first_number AND TO_NUMBER(SUBSTR(big_string_field, -4)) = second_number

    Number positions start with 1 in SQL (not 0 like in code), so that first SUBSTR gets the first 10 characters of my string, and the second gets the last 4 (negative position counts backwards from the end of the string). Note that if there are non-numeric characters in the string, TO_NUMBER would fail, everyone dies, oh the embarrassment.

No comments:

Post a Comment