CODEWITHSUNDEEP
mysql
Detox
Detox

Reputation: 112

replace integer with string in query

I have searched for an answer and have not been able to find one.

I have a table that records an integer instead of text. I am trying to run a query and substitute text for a specific integer.

here are the integers in column cal_referral_type (this column is int) with the correlating text it represents:

DO NOT TRACK|1
Carey Guide |2
Education   |3
Employment  |4
Housing     |5
Medical     |6

I need to run a report to see how many referrals are going to each category but staff get confused with just the number.

If I run a query:

SELECT cal_owner,cal_title,cal_referral_type 
FROM egw_cal 
WHERE cal_referral_type = 3

It shows all referrals to educational services but the column results in a "3" instead of descriptive text "education"

I have read that I can use a CASE Statement such as:

SELECT cal_owner,cal_title, CASE cal_referral_type
WHEN 1 THEN 'NA' ELSE 'other' FROM `egw_cal`

Each time I twist the query a different way, it just errors.

So, is there a way I can have the results of the query show "education" each time the integer "3" is identified? I will expand to include all categories once I can get one to work.

Upvotes: 0

Views: 1914

Answers (1)

Harper Shelby
Harper Shelby

Reputation: 16585

A CASE statement should work, but a better/more flexible approach is to have a lookup table with the integer values and descriptive text, and then join to that table and pull the text out. This makes changes easier to implement, as you update/add to the lookup table instead of altering code. It also helps implement user-friendly interfaces in a cleaner way if you need to do that.

Upvotes: 1

Related Questions