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
– Damien_The_Unbeliever, Commented Mar 1, 2013 at 14:24

Erwin Brandstetter · Accepted Answer · 2024-11-25 17:34:45Z

6

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)

See:

edited Nov 25, 2024 at 17:34

answered Mar 1, 2013 at 14:21

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

CarlosJavier Over a year ago

Thanks a lot, i didnt put the end for saving space :P, but your solution works really well, thanks again.

Collectives™ on Stack Overflow

Using Convert on an ELSE CASE SQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related