CODEWITHSUNDEEP
mysqlsqlreplace
KeyC0de
KeyC0de

Reputation: 5257

MYSQL - Replace 0 with a string and non 0 with another string in SQL query output, without Conditionals

I have a complex string that starts like this: SELECT COUNT(*) AS 'columnName' .... The result will display a positive or 0 number. What i want is in case it's 0 i want it to print string1 (instead of 0) and if it's higher than 0, i want it to print string2 (instead of that number). The caveat is that i don't want to use conditional statements, no ifs or cases. I've been thinking about a replace function, but i don't think it works. After that then maybe a VIEW (virtual table to store the results?) would work? I don't want to alter any original table data in any way, i just want to manipulate the output. I can't figure it out, I'm an SQL beginner. Help?

Upvotes: 2

Views: 458

Answers (2)

Samanvitha - user2519049
Samanvitha - user2519049

Reputation: 384

select COALESCE((select  'string1' as s
                  from table1
                   having count(*) < 0 )
                 ,'string2')

Above query returns string 1 if there are no records in the table else it returns string 2

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The correct approach is to use CASE, but there are other ways:

select elt(1 + least(count(*), 1), 'string1', 'string2')

count(*) almost never returns 0. It only returns 0 when there is no group by and the table has no rows (or all rows have been filtered out by the where.

Upvotes: 2

Related Questions