quote:

---

Originally posted by Venkat144
Now I want to replace null values with empty value(0 or '') without using ISNULL , COALESCE and ANSI_NULLS ..

---

quote:

---

Originally posted by DonAtWork
Then you do not want to use SQL SERVER.

---

SELECT 1 AS NO ,'John' AS Firstname, 'Brown' AS MiddleName, 'Male' AS Gender,GETDATE() AS DOB
INTO myTable
UNION ALL
SELECT 2,'Sara', 'Smith', 'Female',NULL
UNION ALL
SELECT NULL,'Harry', 'Thomas', NULL,GETDATE()
UNION ALL
SELECT 3,'Jennifer', 'Smith', 'Female',NULL
UNION ALL
SELECT NULL,'Jada', 'Reynolds', NULL,GETDATE()

SELECT * FROM myTable

UPDATE myTable SET NO=CASE WHEN NO IS NULL THEN -1 ELSE NO END,
Firstname=CASE WHEN Firstname IS NULL THEN '???' ELSE Firstname END,
MiddleName=CASE WHEN MiddleName IS NULL THEN '???' ELSE MiddleName END,
Gender=CASE WHEN Gender IS NULL THEN '???' ELSE Gender END,
DOB=CASE WHEN DOB IS NULL THEN '1/1/1900' ELSE DOB END

SELECT * FROM myTable

SELECT CASE 
       WHEN no IS NULL  THEN 0
       ELSE no
       END,  

	   CASE 
       WHEN Fitstname IS NULL  THEN ''
       ELSE Fitstname
       END,         
	   CASE 
       WHEN MiddleName IS NULL  THEN ''
       ELSE MiddleName
       END, 

	   CASE 
       WHEN Gender IS NULL  THEN ''
       ELSE Gender
       END, 

	   CASE 
       WHEN DOB IS NULL  THEN 0
       ELSE DOB
       END        
FROM
(
SELECT 1 as no ,'John' as Fitstname, 'Brown' as MiddleName, 'Male' as Gender,GETDATE() as DOB
UNION ALL
SELECT 2,'Sara', 'Smith', 'Female',CAST(null as CHAR(15))
UNION ALL
SELECT null,'Harry', 'Thomas', NULL ,GETDATE()
UNION ALL
SELECT 3,'Jennifer', 'Smith', 'Female',null
UNION ALL
SELECT null,'Jada', 'Reynolds', NULL,GETDATE()
) a

drop table #test
SELECT *  
into #test    
FROM
(
SELECT 1 as no ,'John' as Fitstname, 'Brown' as MiddleName, 'Male' as Gender,GETDATE() as DOB
UNION ALL
SELECT 2,'Sara', 'Smith', 'Female',CAST(null as CHAR(15))
UNION ALL
SELECT null,'Harry', 'Thomas', NULL ,GETDATE()
UNION ALL
SELECT 3,'Jennifer', 'Smith', 'Female',null
UNION ALL
SELECT null,'Jada', 'Reynolds', NULL,GETDATE()
) a

update a
set no = 0
from #test a
where not exists (select * from #test aa where aa.no = a.no)

update a
set Gender = ''
from #test a
where not exists (select * from #test aa where aa.gender = a.gender)

update a
set dob = ''
from #test a
where not exists (select * from #test aa where aa.dob = a.dob)

select * from #test