January 8, 2007 at 9:53 am
As part of an insert statement I feel I want to write something along the lines of the following.
CASE WHEN Col001 NOT LIKE 'NULL' SELECT [Col001] ELSE SELECT NULL,
CASE WHEN Col002 NOT LIKE 'NULL' SELECT [Col002] ELSE SELECT NULL,
Is this good T-SQL?
January 8, 2007 at 9:55 am
The case statement should look like this :
CASE WHEN Col001 NOT LIKE 'NULL' THEN[Col001] ELSE NULL END,
But it can be shortened up with this :
NULLIF(Col001, 'NULL') to get the same result.
January 9, 2007 at 2:34 am
If I would need to write such CASE for some reason, I would prefer not to use LIKE (that only makes sense if you are searching for a pattern, i.e. part of the value... as NOT LIKE 'NULL%') and stick to = / <> :
CASE WHEN Col001='NULL' THEN NULL ELSE Col001 END
Of course NULLIF is the way to go.
January 9, 2007 at 2:53 am
The statement CASE WHEN Col001 NOT LIKE 'NULL' will be looking for the character string 'NULL'. If this is really what you want to do, then it would be more efficient to code WHEN Col001 <> 'NULL'. As a check for eqality is potentially indexible and a ckeck for non-eqality is never indexible, it would be even better to code CASE WHEN Col001 = 'NULL' THEN NULL ELSE [Col001] END
If you want to check if Col001 is a SQL null, then use CASE WHEN Col001 IS NULL THEN...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 10, 2007 at 4:55 am
SELECT NULLIF(Col001, 'NULL') AS NewCol1,
NULLIF(Col002, 'NULL') AS NewCol2
FROM <YourTableNameHere>
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply