CODEWITHSUNDEEP
sqlms-access
Martian
Martian

Reputation: 27

Checking a String value

I would like to incorporate into SQL an if statement determining if the value of a string is equal to "" (and I don't mean an empty string - I mean a string of two quotation marks). How can I do this. I would like the SQL to look something like this:

Select iif(Answer="","No Response", Answer)
From tblAnswers

Upvotes: 0

Views: 153

Answers (10)

onedaywhen
onedaywhen

Reputation: 57023

SELECT IIF(Answer = CHAR(34) & CHAR(34), 'No Response', Answer)
  FROM tblAnswers;

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Access' database engine will recognize string literals enclosed in either single or double quotes. So although this one will work ...

Select IIf(Answer = """""", "No Response", Answer)
From tblAnswers;

... single quotes would make it clearer, IMO, and I'd be less likely to lose track of the proper number of double quotes when my eyes are tired. So I'd do this instead ...

Select IIf(Answer = '""', 'No Response', Answer)
From tblAnswers;

If you also want to output No Response when Answer is Null or an empty string, try this ...

Select IIf(Answer = '""' OR Len(Answer & "") = 0, 'No Response', Answer)
From tblAnswers;

Upvotes: 2

James Johnson
James Johnson

Reputation: 46047

SELECT ISNULL(NULLIF(Answer,'""'), 'No Response') Answer
FROM tblAnswers

If the column contains two single quotes, use this:

SELECT ISNULL(NULLIF(Answer, ''''), 'No Response') Answer
FROM tblAnswers

Upvotes: 0

user747858
user747858

Reputation:

For Oracle

select decode(answer,"''''","No response",answer) from tblanswers

Upvotes: 0

Justin Niessner
Justin Niessner

Reputation: 245399

It looks like you're using Microsoft Access (judging by the use of iif). To escape double quotes, you should be able to do:

Select iif(Answer = """""", "No Response", Answer)
From tblAnswers

Upvotes: 2

Barry Kaye
Barry Kaye

Reputation: 7761

Try:

SELECT CASE WHEN Answer = '' THEN 'No Response' ELSE Answer END FROM tblAnswers

Upvotes: 2

Curtis
Curtis

Reputation: 103348

SQL Server:

SELECT ISNULL(NULLIF(Answer, ""), "No Response")

Upvotes: 2

Bohemian
Bohemian

Reputation: 424983

Try this:

select if(Answer='""', 'No Response', Answer) as Answer
From tblAnswers

The exact syntax will vary with your actual database. You may need a CASE if your DB doesn't support if

Upvotes: 0

Caimen
Caimen

Reputation: 2619

You need to use an escape character. In SQL I believe it would be 

Answer = "\"\"";

Upvotes: 0

Neil Knight
Neil Knight

Reputation: 48537

SELECT CASE
       WHEN Answer = '""' THEN 'No Response'
       ELSE Answer
       END AS Answer
  FROM tblAnswers

Upvotes: 6

Related Questions