I need to SELECT a set of rows of a given table whose structure looks like - 1º 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

  • Can you show the exact table structure ? Run SHOW CREATE TABLE tblname\G and show the top 10 lines and the bottom 10 lines. Commented Apr 1, 2015 at 18:37
  • 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.
    – Nico
    Commented Apr 1, 2015 at 18:45

1 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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.