displaying something else in results instead of value in DB

[rleroux]

Hi,

I am retrieving State abbreviations from the database (e.g. CA, WA, TX). What I would like to do is that when any of the state abbreviations are encountered, I will display the full state name intead of the abbreviation.

What do I need in my statement to do this? I'm playing with IF statements with no success

e.g.
SELECT address, city, state
FROM atable
WHERE city like 'Culver%'


instead of seeing
address city state
555 Culver City CA

I would like to display
address city state
555 Culver City California


Tnx.

[bergy]

If you have another table containing the definitions of the abbreviations you could do something like this:

SELECT atable.address, atable.city, abbrevtable.stateFull
FROM atable
INNER JOIN abbrevtable ON atable.state = abbrevtable.stateAbbrev
WHERE atable.city LIKE 'Culver%'

That statement assumes you have a table called abbrevtable with an entry like this in it:

stateAbbrev            stateFull
-----------            ---------
CA                     California

Hi,

I am retrieving State abbreviations from the database (e.g. CA, WA, TX). What I would like to do is that when any of the state abbreviations are encountered, I will display the full state name intead of the abbreviation.

What do I need in my statement to do this? I'm playing with IF statements with no success

e.g.
SELECT address, city, state
FROM atable
WHERE city like 'Culver%'


instead of seeing
address city state
555 Culver City CA

I would like to display
address city state
555 Culver City California


Tnx.

[mabubakarpk]

If you have not any reference table then you can write this query

 
select City,case City when 'NY' then 'NewYork'
                            when 'LHR' then 'Lahore'
                            else 'Not Defined'  end as CityFullName 
                            from City