Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to 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
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.4K
Edited 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 at 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 at 22-Jan-13 11:42am
   
Have you tried to use ISNULL(e.industry_type,'some default value if null') as suggested?
ntitish at 23-Jan-13 5:09am
   
above reply is that only sir, after modifying only i sended the reply...
Vyacheslav Voronenko at 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 at 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 at 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 at 24-Jan-13 2:00am
   
by clicking on the error it is showing only select statement...
Vyacheslav Voronenko at 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 at 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
0 OriginalGriff 6,651
1 Sergey Alexandrovich Kryukov 6,400
2 CPallini 5,230
3 George Jonsson 3,574
4 Gihan Liyanage 2,542


Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 24 Jan 2013
Copyright © CodeProject, 1999-2014
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