SQL - Replacing an is null integer with a varchar

Question

I am trying to replace a column with a new varchar string if there is null values in it in a select statement:

(personid + ISNULL(personid, 'no person'))

I'm not trying to update it, only show the value as 'no person' in the query result.

But I am getting an error saying:

the conversion failed when converting the varchar value to a data type int.

How do I get past this?

Tab Alleman · Accepted Answer · 2014-10-02 17:03:47Z

2

Replace this:

(personid+ISNULL(personid,'no person'))

With this:

(ISNULL(CAST(personid as varchar(31)),'no person')) AS personId

edited Oct 2, 2014 at 17:03

answered Oct 2, 2014 at 14:54

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

3 Comments

user2334014 Over a year ago

Sorry, I just checked now and the field name is actually stored as 'NULL' instead of not showing anything. How would I go about converting that to something else in the query result? Thanks !

user2334014 Over a year ago

But this still worked as the personid field is INT so I had to change it anyway! Thanks a lot bro!

Tab Alleman Over a year ago

I'm confused as to where you say they are storing 'NULL'. If the personId field is INT, they can't be storing the string 'NULL' in it. If you mean the column header doesn't have a name, that can be fixed with a alias. See my edited answer above.

Collectives™ on Stack Overflow

SQL - Replacing an is null integer with a varchar

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related