[RESOLVED] isnullorwhitespace?

[sapator]

MSSQL 2008.
I mean I can do in with when but how can I simulate a nullorwhispace when creating a view?

Currently I'm doing it this way. Is it acceptable?

Create View 

SELECT...

case when person_suburb ='' THEN NULL
ELSE ISNULL( Cast( SUBSTRING(person_suburb,Charindex('FD',person_suburb)+5,8) as date),null) END as FirstDate

FROM ...

Not I'm also breaking a nvarcahr substring to date the issue is that if I use just ISNULL, any empty value '' will produce '1900-01-01' but I need it to produce null.

[Zvoni]

hmm.... to get an idea for it, i'd need to know what three possible patterns for person_suburb look like
Because to me person_suburb can have 3 possible "patterns"
1) person_suburb is an empty string '' --> return NULL
2) person_suburb is NOT an empty string, in which case you look for a string "FD", move 5 characters to the right of it, and from there look for an 8-digit Date-representation
2a) there is an actual date --> return the Date
2b) there is not a date --> return NULL

That correct?

[sapator]

I guess. The date is always represented as a string I have to cast.
The values are NULL, '' or longstring. The ideal would be an existing isnullorwhitespace as in .net but there is non in SQL.

Edit.
After more careful evaluation what I'm using may break sargability on an already non sargable CAST , so a more sargable select might be:

  case when (person_suburb is null or DATALENGTH(person_suburb) <=0) THEN NULL
  ELSE Cast( SUBSTRING(person_suburb,Charindex('FD',person_suburb)+5,8) as date) END as Firstdate

[Zvoni]

I guess. The date is always represented as a string I have to cast.
The values are NULL, '' or longstring. The ideal would be an existing isnullorwhitespace as in .net but there is non in SQL.

Edit.
After more careful evaluation what I'm using may break sargability on an already non sargable CAST , so a more sargable select might be:

  case when (person_suburb is null or DATALENGTH(person_suburb) <=0) THEN NULL
  ELSE Cast( SUBSTRING(person_suburb,Charindex('FD',person_suburb)+5,8) as date) END as Firstdate

So, in case person_suburb is NOT NULL and NOT '', it's always something like
"SomeValueFDabc20240429"

Then i'd rather go with

CASE WHEN LENGTH(TRIM(COALESCE(person_suburb, '')))=0 THEN NULL 
ELSE CAST blablabl ...... END

Note: LENGTH and TRIM are the Function-Names in SQL for DB2 (the only system i have for testing), so they might be called differently in MSSQL

1) COALESCE returns the first NON-Null-Argument --> in case person_suburb IS NULL it returns '', otherwise it returns person_suburb
2) If person_suburb is something like ' ' (a lot of whitespace), the coalesce returns that (because it's not NULL), and the Trim trims it down to ''
3) Checking the Length of whatever was returned by TRIM. If it's 0 then return NULL else branch off to the SUBSTRING to return the Date

[sapator]

I think either would do (it's ltrim and LEN on the function names).
They execute the same and query plan is the same. I was expecting some query plan deviation with COALESCE but I get none. Maybe it works maybe it's because the table is a view and it cannot understand the execution plan.
Point being that I need a CASE so probably both will do.

Thanks.