SQL column/row format question

my query returns a column that can hold types of real estate. Values can be condo or duplex or house and so on. Instead of displaying condo, I just want a C in the column. My plan was to use a huge case/when structure to cover all the开发者_运维问答 cases, is there an easier way? Just displaying the first letter in upper case wont work by the way, because sometimes that rule cant be applied to create the short code. Duplex for example is DE...

Thanks :-)

If you don't want to use a CASE statement how about creating a lookup table to map the column value to the lookup code you want. Join on to this in your query.

NB - Only worth considering if your query is running over a fairly small resultset or you'll hit performance issues. Indexing the column would help.

Some other options are depending on your DB server features:

1. Use a UDF to do the conversion.
2. Computed column on the source table.

Helper table with a column for each shorthand matching the long string?

The obvious thing to do would be to have another table which maps your value to a code, to which you can then join your results. But it smells a bit wrong. I'd want to join this other table to key values, not strings (which I assume aren't key values)

Why dont you use a decode function in sql

select decode(your_column_name,"condo","C",your_column_name) from table