I want to have compatible SQL for both Oracle database and Microsoft SQL server.
I want a compatible SQL expression that will return true for not null and not empty strings.
If I use:
column <> ''
it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)
If I use:
len(column) > 0
it will work on Microsoft SQL server but not on Oracle database (since it uses length() )
I think the key here is to differentiate between the case when the empty string is equivalent to NULL and when it isn't:
WHERE CASE WHEN '' = '' THEN -- e.g., SQL Server this is true
CASE WHEN col <> '' AND col IS NOT NULL THEN 'Y'
ELSE 'N'
END
WHEN COALESCE(col,NULL) IS NOT NULL THEN 'Y' -- Not SS, e.g., Oracle
ELSE 'N'
END = 'Y';
If the first case is true then empty string is not the same as null, and we have to test for both string being not null and string not being the empty string. Otherwise, our task is easier because empty string and null evaluate the same.