PROBLEM WITH NULL WHEN CONCATENATE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PROBLEM WITH NULL WHEN CONCATENATE

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-22 : 06:06:52

I m Creating One table
Create table Empname
( NAME1 Char(10) NULL,
NAME2 Char(10) NULL,
NAME3 Char(10) NULL
)
AFTER That Inserting The data in Empname table With Some Null Values
After that I m Fetch the records
SELECT (NAME1 + NAME2 + NAME3) AS NAME FROM Empname)
GET ME RECORDS LIKE THIS
'yaman NULL GUPTA'
'RAJ KUMAR NULL'
Why NULL IS COMING

Yaman

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-22 : 06:12:26

What is your setting for CONCAT_NULL_YIELDS_NULL?

Also show us how you are inserting NULLs in table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 06:13:21

Because you have NULL values in some of fields. Concatenating with NULL value always yields NULL unless you have concatenate null yields null property set to off. SO do like this

SELECT COALESCE(NAME1,'')+COALESCE(NAME2,'')+COALESCE(NAME3,'') AS NAME FROM Empname

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-22 : 06:14:31

HOW I AM CHECK THIS SETTING ?

What is your setting for CONCAT_NULL_YIELDS_NULL?

Also show us how you are inserting NULLs in table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
[/quote]

Yaman

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-22 : 06:17:19

quote:
Originally posted by yaman

I m Creating One table
Create table Empname
( NAME1 Char(10) NULL,
NAME2 Char(10) NULL,
NAME3 Char(10) NULL
)
AFTER That Inserting The data in Empname table With Some Null Values
After that I m Fetch the records
SELECT (NAME1 + NAME2 + NAME3) AS NAME FROM Empname)
GET ME RECORDS LIKE THIS
'yaman NULL GUPTA'
'RAJ KUMAR NULL'
Why NULL IS COMING

Yaman

you might have inserted 'NULL' into name2 for first record
and 'Null' into name3 for second record

please send me insert statements which u used to insert data

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-22 : 06:18:34

quote:
Originally posted by yaman

HOW I AM CHECK THIS SETTING ?

What is your setting for CONCAT_NULL_YIELDS_NULL?

Also show us how you are inserting NULLs in table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Yaman
[/quote]

EXEC sp_dboption 'db1', 'CONCAT_NULL_YIELDS_NULL'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 06:24:09

quote:
Originally posted by yaman

HOW I AM CHECK THIS SETTING ?

What is your setting for CONCAT_NULL_YIELDS_NULL?

Also show us how you are inserting NULLs in table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sp_dboption 'Yourdatabasename','concat null yields null'

Yaman
[/quote]

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-22 : 06:32:31

SIR , setting for CONCAT_NULL_YIELDS_NULL IS OFF
and If i am not insert any value then bydefault take NULL Value
and Sir
How I am SET CONCAT_NULL_YIELDS_NULL ON

quote:
Originally posted by harsh_athalye

What is your setting for CONCAT_NULL_YIELDS_NULL?

Also show us how you are inserting NULLs in table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Yaman

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-22 : 06:36:29

Then set it to ON by using following command :

SET CONCAT_NULL_YIELDS_NULL ON

and then use Visakh's query with Coalesce() to resolve your problem.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-22 : 06:43:51

Thank u Very Much Sir

I got My Solution

Yaman

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-22 : 06:44:24

Thank u Very Much Sir

I got My Solution

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 06:57:30

quote:
Originally posted by raky

quote:
Originally posted by yaman

I m Creating One table
Create table Empname
( NAME1 Char(10) NULL,
NAME2 Char(10) NULL,
NAME3 Char(10) NULL
)
AFTER That Inserting The data in Empname table With Some Null Values
After that I m Fetch the records
SELECT (NAME1 + NAME2 + NAME3) AS NAME FROM Empname)
GET ME RECORDS LIKE THIS
'yaman NULL GUPTA'
'RAJ KUMAR NULL'
Why NULL IS COMING

Yaman

you might have inserted 'NULL' into name2 for first record
and 'Null' into name3 for second record

please send me insert statements which u used to insert data

SQL Server is by default case insensitive. Also 'NULL' is not same as NULL.

see this:-

declare @test table
(val varchar(10)
)
insert into @test
select 'Test'
union all
select 'test'
union all
select 'TeSt'
union all
select 'TEST'


select * from  @test where val='test'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources