Replace NULL with blank value (SQL Server)

Question

Replace NULL value with blank value

While trying to convert NULL to blank value by using a CASE statement.

Note: sampCol is numeric IDs

,CASE WHEN sampCol IS NULL THEN '' ELSE sampCol END as sampCol

I keep getting the following error:

Error converting data type varchar to numeric.

John Cappelletti · Accepted Answer · 2019-05-13 17:27:13Z

6

You're mixing numeric and string values.

A NULL is not an empty string. It is the absence of value.

Items like this should really be relegated to the presentation layer, but if you must, try the following cheat.

... , concat('',sampCol) as sampCol ...

edited May 13, 2019 at 17:27

answered May 13, 2019 at 17:23

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

1.618 Over a year ago

would this go under there WHERE statement?

John Cappelletti Over a year ago

@PhilK Nope. This is the column. I have no idea what your WHERE statement is

granadaCoder Over a year ago

"should really be relegated to the presentation layer" << 100% agree. (to the person who wrote the original question...PLEASE get this voodoo conversion out of your datalayer. this is a presentation concern.

1.618 Over a year ago

Sorry, I meant WHERE clause, but yes I just got what you meant. But yeah, that totally worked. Thanks

Thom A Over a year ago

Although this does work, this does now mean that 10 < 2, which is just wrong. The caveats have been given here though; this doesn't belong in the RDBMS side of things. But this should definitely not go in the WHERE @PhilK. It'll ruin any performance your query had.

Sean Lange · Accepted Answer · 2019-05-13 17:20:50Z

This is because a case expression can return one and only datatype according to datatype precedence. You can't convert an empty string a numeric datatype. What you are trying to do sounds like it belongs at the presentation layer, not the data layer. Let NULL happen!!!

There is always a way. But in this case you shouldn't. Your presentation logic does not belong in the database.

Arulkumar · Accepted Answer · 2019-05-13 17:27:21Z

Using COALESCE() with CAST() will help:

SELECT COALESCE(CAST(SampCol AS VARCHAR (10)), '') AS SampCol FROM TestTable

Here instead of VARCHAR (10), you can change the required length as per your business need.

Demo on db<>fiddle

Paul Roub · Accepted Answer · 2020-04-27 22:21:19Z

Adding to Shawn May's answer. Three left brackets and only 2 right brackets will never work. Try:

ISNULL(CAST(sampCol AS VARCHAR(100)),'')

Maybe add the field name using AS so you can use it in your app.

ISNULL(CAST(sampCol AS VARCHAR(100)),'') as samplColName

Brian Alvarez · Accepted Answer · 2020-01-03 16:17:21Z

Script to Fix all null values into database

/By Brian Alvarez Ayala/ email - [email protected] website - w w w . a s e s o r i a r e m o t a . c o m

Declare @COLUMN_NAME varchar(40) Declare @DATA_TYPE varchar(40) Declare @TABLES_NAMES varchar(40) Declare @TABLE_SCHEMA varchar(40) Declare @IS_NULLABLE varchar(3)

Declare @SqlCMD varchar(MAX) Declare tablas cursor for select t.TABLE_SCHEMA ,t.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.IS_NULLABLE from INFORMATION_SCHEMA.TABLES t inner join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME = t.TABLE_NAME and c.TABLE_SCHEMA = t.TABLE_SCHEMA open tablas fetch next from tablas into @TABLE_SCHEMA,@TABLES_NAMES,@COLUMN_NAME,@DATA_TYPE,@IS_NULLABLE while @@FETCH_STATUS =0 begin
if (@DATA_TYPE = 'numeric' or @DATA_TYPE = 'int') and @IS_NULLABLE='YES' begin set @SqlCMD ='Update ' + @TABLE_SCHEMA+'.'+@TABLES_NAMES + ' set ' + @COLUMN_NAME +' = 0 where ' + @COLUMN_NAME + ' is null' print @sqlcmd end

 if (@DATA_TYPE = 'nvarchar' or @DATA_TYPE = 'varchar' or @DATA_TYPE = 'char') and @IS_NULLABLE='YES' begin set @SqlCMD ='Update ' + @TABLE_SCHEMA+'.'+@TABLES_NAMES + ' set ' + @COLUMN_NAME + ' = ' + ''' ''' + ' where ' + @COLUMN_NAME + ' is null' print @sqlcmd end if (@DATA_TYPE = 'bit') and @IS_NULLABLE='YES' begin set @SqlCMD ='Update ' + @TABLE_SCHEMA+'.'+@TABLES_NAMES + ' set ' + @COLUMN_NAME + ' = ' + '''False''' + ' where ' + @COLUMN_NAME + ' is null' print @sqlcmd end fetch next from tablas into @TABLE_SCHEMA,@TABLES_NAMES,@COLUMN_NAME,@DATA_TYPE,@IS_NULLABLE end

close tablas deallocate tablas

Please, fix the formatting of your answer. Furthermore a little more description would help any reader to better understand your code.

alexherm · Accepted Answer · 2019-05-13 17:24:00Z

-1

 ..CASE WHEN sampCol IS NULL THEN to_number('') ELSE sampCol END as sampCol

answered May 13, 2019 at 17:24

alexherm

1,3502 gold badges21 silver badges35 bronze badges

3 Comments

1.618 Over a year ago

to_number('') is not a recognized built-in function name.

alexherm Over a year ago

Ah SQL Server. ..CASE WHEN sampCol IS NULL THEN cast('' as number) ELSE sampCol END as sampCol

Shawn Over a year ago

There are functions that handle this exact situation. No need for a CASE. For T-SQL, use ISNULL(CAST(sampCol AS VARCHAR(100),'') or generic SQL COALESCE(CAST(sampCol AS VARCHAR(100),'').

Collectives™ on Stack Overflow

Replace NULL with blank value (SQL Server)

6 Answers 6

5 Comments

2 Comments

Comments

Comments

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

2 Comments

Comments

Comments

1 Comment

3 Comments

Related