CODEWITHSUNDEEP
sqloracle-databaseplsql
Bhushan
Bhushan

Reputation: 215

How to use Trim in sql case

select
distinct ca.CASE_KEY,
ca.priority,
ca.lock_flag,
ca.alternate_case_id as ALTERNATIVE_CASE_ID,
ca.case_identifier as case_identifier

from cases ca

The above query returns alternate_case_id as 'null' because there is a string 'null' in db.

the client wants to see nothing if it is null. How can I use sql case along with Trim so that this bug will work for data like ' null'. Please help.

Upvotes: 2

Views: 9156

Answers (3)

Nick Rolando
Nick Rolando

Reputation: 26167

Try this

select
distinct ca.CASE_KEY,
ca.priority,
ca.lock_flag,
CASE trim(ca.alternate_case_id)
  WHEN 'null' THEN ''
  ELSE ca.alternate_case_id
END AS ALTERNATIVE_CASE_ID,
ca.case_identifier as case_identifier

from cases ca

plsql trim()
Case Statement

Upvotes: 4

xQbert
xQbert

Reputation: 35323

replace(alternate_case_ID,'null','')

though you should ask why a NULL string is a is a stored value in the first place...

you could update all fields that have null string to the actual NULL value.

Lastly,

Are you SURE the null string is being displayed and it's not just a feature of the tools your using to display "NULL"

Where are they seeing "NULL" in a webpage? report? Form, SQL Developer (which by default shows (null) when a null value is encountered..

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Not sure if the string was actually NULL, or contained the text 'null'... so I handled both cases...

select
distinct ca.CASE_KEY,
ca.priority,
ca.lock_flag,
nvl(NULLIF(ca.alternate_case_id, 'null'), '') as ALTERNATIVE_CASE_ID,
ca.case_identifier as case_identifier
from cases ca

Upvotes: 2

Related Questions