Using Convert on an ELSE CASE SQL query

Question

I am performing a SQL query in Postgres, which selects a numeric field. I need to display a string value as the result of this SELECT, so I am using a CASE statement like this:

Select 
case numeric_field
when 100 then 'some string'
when 200 then 'some other string'

The problem is that if the numeric field has any other value (like 300 for example), I need to display this value (as a string of course). I try to put a CONVERT on the else like this

...
else CONVERT(varchar(10),numeric_field)

But it didn't work. How do I do this?

`CONVERT` is, so far as I'm aware, only in the T-SQL dialect of SQL (found in MS SQL Server and Sybase) — Damien_The_Unbeliever, Mar 01 '13 at 14:24

Erwin Brandstetter · Accepted Answer · 2013-03-01T14:27:40.227

5

SELECT CASE numeric_field
          WHEN 100 THEN 'some string'
          WHEN 200 THEN 'some other string'
          ELSE numeric_field::text
       END AS result

Your statement was incomplete, END was missing. Read the manual here.
To output a numeric field as text, just cast it to text: numeric_field::text, which is the Postgres specific short form of the SQL standard call:
```
cast (numeric_field AS text)
```

edited Mar 01 '13 at 14:27

answered Mar 01 '13 at 14:21

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks a lot, i didnt put the end for saving space :P, but your solution works really well, thanks again. – CarlosJavier Mar 01 '13 at 14:29

Using Convert on an ELSE CASE SQL query

1 Answers1