MySQL - How to replace a given value from multiple columns when using SELECT?

Question

I need to SELECT a set of rows of a given table whose structure looks like - 1o line is the name of columns

 A01 A02 A03 A04 A05 ... A200
 99999 99999 10 753 99999 99999

As you can see, it has about 200 columns. It uses 99999 to model a NULL or undefined value. So, in my SELECT, i need to replace those values with NULL or even a empty String. Is it possible by using a single SELECT or should i use something else ?

ATT: you could suggest UPDATE those values with NULL - and then SELECT -, but it is a legacy system which we cannot modify them

• RolandoMySQLDBA: Can you show the exact table structure ? Run SHOW CREATE TABLE tblname\G and show the top 10 lines and the bottom 10 lines.
• Nico: You can create a view that expose the table with replacement of 99999 by NULL and issue your select in the new application on the view instead of the real table.

Answer 1

SELECT IF(A01 = 99999, NULL, A01) AS A01,
 IF(A02 = 99999, NULL, A02) AS A02,
 ...
 FROM ...

(For empty string, use '' instead of NULL.)