All geek questions in one place

Using clipping in an empty string in where where in ORACLE SQL

I am trying to understand why the SQL statement below does not return a value in oracle

Select 'do' from dual where trim(' ') = ''

Where

 Select 'do' from dual where trim(' a ')='a'

returns do.

+4
source share
2 answers

because trim(' ') returns null , not ''

SQLFiddle example

In Oracle 8, there is no such thing as a zero-length string. Any string of zero length, either from a function call or from a literal '', is treated as null.

A source

+6
source

A string of zero length is treated as zero, and you cannot compare zeros through (c) equality in Oracle, since they are processed in a special way.

Link to Doc: NULLS in conditions

Hence this one is not expected to work:

 Select 'do' from dual where trim(' ') = ''

Try this instead:

 10:39:58 SYSTEM@dwh-prod > select * from dual where trim(' ') is null 10:40:02 2 / D - X Elapsed: 00:00:00.07 10:40:04 SYSTEM@dwh-prod > select * from dual where '' is null 10:40:11 2 / D - X Elapsed: 00:00:00.02 10:40:11 SYSTEM@dwh-prod >
+1
source

Source: https://habr.com/ru/post/1442886/

More articles:


All Articles