Let's say I have a column whose values are all 2-digit integers, e.g. 82 (though it's actually a varchar field).
From now on, the column will be able to have 2-digit as well as 3-digit
integers. In the application that uses these values, a value of the format x0y is considered to be the same as xy. E.g. values 82 and 802 are considered to be the same, 45 and 405 are considered to be the same, etc.
Both formats still have to be supported in order to be compatible with historical data - I'm not in control of the database and unfortunately existing 2-digit data won't be converted to 3-digit.
The application has many, many separate places where it reads from that table, e.g. select colname from sometable where.... And in many, many separate places it uses the same code (hard-coded) to split up each value into 2 digits, e.g. for value 82, it will split it up into the digits 8 and 2, and make use of them.
Yep, that query and that code are scattered all over the place and are not in a common subroutine :( . So it would take a lot of work to change all of them.
Question: Is there any way to specify the SQL query so that, when it sees a digit of the format xy, it automatically returns it as x0y? (e.g. if one row has the value 82 and another has the value 802, the SQL query fudges the returned rows so both of them have the value 802.) Maybe with regular expressions somehow?
Even better, is there any way to do that on the database side without changing the query itself, e.g. with a trigger perhaps?
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
