Click here to Skip to main content
12,396,461 members (67,136 online)
Rate this:
 
Please Sign up or sign in to vote.
how to replace the null value with space while fetching the data from database.
below i given my code.
//sql query
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.
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.
<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
ntitish1.9K
Updated 23-Jan-13 18:41pm
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
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+'<br>'+r.Email_Id +'<br>'+'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+'<br>'+r.Email_Id +'<br>'+'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+'<br>'+r.Email_Id +'<br>'+'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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 24 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100