how to replace the null value with space while fetching the data from database

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

how to replace the null value with space while fetching the data from database.
below i given my code.
//sql query

SQL

select r.login_id,r.First_Name,r.Email_Id,e.industry_type,'<b>Name-</b>'+r.First_Name+'<br>'+r.Email_Id +'<br>'+'<b>IndustryType-</b>'+e.industry_type as fulldetails
from Registration as r
inner join Experience_Detail as e
on r.Login_Id=e.Login_Id and status='Active' and Designation='User'

//in this e.industry_type field having null values
when i am binding the data of all the fields into one field as fulldetails.if there is null value in any field means fulldetails is showing null values for example you can see below.

VB

login_id| First_Name   | Email_Id     | industry_type  |   fulldetails
-----------------------------------------------------------------------------------
nitish     | nitish       |pv@gmail.com  |    IT    | nitish,nitish,pv@gmail.com,IT

teja       | teja         |pv@gmail.com  |   null   |null

//But i want the table like this below,for that what i have to do.

XML

<pre lang="vb">| First_Name   | Email_Id     | industry_type  |   fulldetails
-----------------------------------------------------------------------------------
nitish     | nitish       |pv@gmail.com  |    IT    | nitish,nitish,pv@gmail.com,IT

teja       | teja         |pv@gmail.com  |   null   |teja,teja,pv@gmail.com,</pre>

Posted 19-Jan-13 21:46pm

ntitish

Updated 23-Jan-13 18:41pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vyacheslav Voronenko · Accepted Answer · 2013-01-19T21:49:00

Solution 1

You can use ISNULL sqlserver function, to replace nulls with value of your choice.

More details:

http://msdn.microsoft.com/en-us/library/ms184325.aspx[^]

Posted 19-Jan-13 21:49pm

Vyacheslav Voronenko

Comments

ntitish 22-Jan-13 7:32am

//this was my code but it is showing error

select r.login_id,r.First_Name,r.Email_Id, isnull(e.industry_type,' ') as e.industry_type,'Name-'+r.First_Name+' '+r.Email_Id +' '+'IndustryType-'+e.industry_type as fulldetails from Registration as r inner join Experience_Detail as e on r.Login_Id=e.Login_Id and status='Active' and Designation='User'

Vyacheslav Voronenko 22-Jan-13 11:42am

Have you tried to use ISNULL(e.industry_type,'some default value if null') as suggested?

ntitish 23-Jan-13 5:09am

above reply is that only sir, after modifying only i sended the reply...

Vyacheslav Voronenko 23-Jan-13 8:13am

And what is result of the query? I see that you still have e.industry_type w/o ISNULL in another clause.

ntitish 24-Jan-13 0:45am

//this was my code
select r.login_id,r.First_Name,r.Email_Id, isnull(e.industry_type,'') as e.industry_type,'Name-'+r.First_Name+' '+r.Email_Id +' '+'IndustryType-'+e.industry_type as fulldetails
from Registration as r
inner join Experience_Detail as e
on r.Login_Id=e.Login_Id and status='Active' and Designation='User' and r.login_id!=@ParentLogin_Id

//but i am getting an error like this

Msg 102, Level 15, State 1, Procedure Alumniproc, Line 653
Incorrect syntax near '.'.

Vyacheslav Voronenko 24-Jan-13 1:40am

Again, is it error in this select? Message shows error in the stored procedure. Is it hard to execute just this single select, to check if IFNULL works?

ntitish 24-Jan-13 2:00am

by clicking on the error it is showing only select statement...

Vyacheslav Voronenko 24-Jan-13 2:10am

Sir, there are different methods of debug. Use some! If it shows on select - extract it, run separately, simplify until the moment when it is clear what's wrong. This has nothing to do with original question.

ntitish 24-Jan-13 4:51am

i got the answer sir thanks for ur help....this was my query.

select r.login_id,r.first_name,'Name-'+r.First_Name+' '+r.Email_Id +' '+'IndustryType-'+isnull(e.industry_type,'Not-Mentioned') as fulldetails
from Registration as r
inner join Experience_Detail as e
on r.Login_Id=e.Login_Id and status='Active' and Designation='User'and r.login_id!=@ParentLogin_Id