CODEWITHSUNDEEP
mysql
SriHarish
SriHarish

Reputation: 311

Display String for a Number in a query result

A Query result is just Numbers and it would be easy to read thru and understand if they are String. How to mark the String equivalent for a number in the query result.

The Mapping for Numbers vs String has to be in the Query.

select num from table; Query Result is 9,2,4,7. String literals are to be mapped to this numbers. the num range is from {0-9} 0-Prepaid, 1 -postpaid like wise

Upvotes: 1

Views: 1264

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can use CASE expression for this like so:

SELECT 
  ...
  CASE 
    WHEN 1 THEN 'Postpaid'
    WHEN 2 THEN 'prepaid'
    ...
   END
...

You can do this also using a temp table for example:

DECLARE @t Table(NumberToReplace INT, replacewith VARCHAR(50));
INSERT INTO @t VALUES
(1, 'Postpaid'),
(2, 'Prepaid'),
...

Then you can JOIN the two tables to replace the numbers with the corresponding string:

SELECT 
  t1.Column1, t1.Column1, t2.replacewith
FROM YourTable t1
LEFT JOIN temp t2 ON t1.NumberToReplace = t2.NumberToReplace

I used LEFT JOIN to give NULL values for the numbers that had no corresponding strings in the other table.

Here is a demo in sQL fiddle

Upvotes: 2

Mihai Matei
Mihai Matei

Reputation: 24276

Is this what you want? 

SELECT id, IF(my_field REGEXP '[digit]', 'is_string', 'is_numeric') as result
FROM my_Table

Check out this http://sqlfiddle.com/#!2/48a6c/1

Upvotes: 1

Related Questions