SQL string comparison -how to ignore blank spaces

Question

I have prepared an SQL query that I will have to run on several databases (Oracle and Sybase) where some data might be stored differently.

I have noticed that one of the differences in data storage is the blank string. For example, in the column PRODUCT_TYPE below, please have a look at the second record:

This "empty string" (the data type is CHAR(15)) circled in red is equal to '' in some of the databases, whereas it's equal to ' ' to some others. The length is never constant and there are several fields that behave as such.

So, since I need to filter on these "empty strings", I should change the following statement in my WHERE clause:

WHERE PRODUCT_TYPE = ''

...because the above will take the ' ' string as different than '' even if "functionally" speaking is not. I would hence like to make the statement in a way that it "ignores white spaces", i.e. ' ' is equal to '' that is equal to ' ' etc. How should I do this change in order to make it work?

I have tried the simple replacing approach:

WHERE REPLACE(PRODUCT_TYPE,' ','') = ''

...but it doesn't seem to work, probably because I should use a different character. For sake of testing, inside the ' below there is a copied-pasted example of what I find in these "empty strings":

'               '

Ideally, it should be a "non-specific SQL" solution since I will have to run the same query on both Oracle and Sybase RDBMS. Any idea?

Answer 1

You can use trim on the column.

where trim(product_type) is null

The above is not DBMS-independent, since Sybase does not provide the trim function. However, the below approach will work both in Sybase and Oracle:

where rtrim(ltrim(product_type)) is null