SQL replace value

Question

I have some data stored in a table about a client's language preference but it is stored as an integer where 8 = english and 9 = french etc. The reason for this I assume is that the integers are keys in another table that links them to the string describing them.

Unfortunately I only have access to the first table and not the other one or it would be a simple matter of doing a JOIN so is it possible to write a SELECT query that maps the 8 to English and 9 to French and so on?

I'm imagining something like this..

SELECT clientName, FUNCTION(languagePreference, ((8, "English"), (9, "French"))) 
FROM table

So instead of this..

clientName languagePreference

Dave          8    
Emmanuelle    9    
Luc           9    
John          8

I get this...

clientName languagePreference

Dave         English
Emmanuelle   French
Luc          French
John         English

also anyone know how to represent tabular data in StackOverflow posts? — Devin Crossman
– Devin Crossman, Commented Dec 11, 2012 at 21:22
There are certainly options to do the inline conversion as you describe above, but it will likely be server specific. What DBMS are you using? — Mike Clark
– Mike Clark, Commented Dec 11, 2012 at 21:22
This particular situation needs to work using TSQL on SQL Server 2008 but it would also be useful to know how to do this in MySQL — Devin Crossman
– Devin Crossman, Commented Dec 11, 2012 at 21:25
Re tabular data: the most common approach is to format it in a code block and use spaces to make everything line up, since code formatting uses a fixed width for all characters. — Esoteric Screen Name
– Esoteric Screen Name, Commented Dec 11, 2012 at 21:27

Indi · Accepted Answer · 2012-12-11 21:25:51Z

2

TSQL:

SELECT clientName, 
  CASE languagePreference
       WHEN 8 THEN 'English'
       WHEN 9 THEN 'French'
       ... --Go on like this if you have more languagePreference keys
  END as languagePreference
FROM table

answered Dec 11, 2012 at 21:25

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francis P · Accepted Answer · 2012-12-11 21:25:38Z

1

Using TSQL, use a CASE

SELECT clientName, 
       CASE WHEN (languagePreference=8) THEN 
               'English' 
       ELSE 
            CASE WHEN (languagePreference=9) THEN
                'French'
            ELSE
                'Unknown'
            END
       END as languagePreference
FROM table

answered Dec 11, 2012 at 21:25

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

2 Comments

Indi Over a year ago

You can put everything in a single CASE no need for nesting

Francis P Over a year ago

Yes just saw that, good to know! This is mainly the reason why I interract on SO...learn!

Collectives™ on Stack Overflow

SQL replace value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related