NULL value ADDITION with TEXT value PLEASE HELP

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

SQL Server 2000 Forums

SQL Server Development (2000)

NULL value ADDITION with TEXT value PLEASE HELP

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-28 : 13:37:34

HELLO friends,
This is my emp table:
empid emptitle empfirst emplast
----- -------- -------- -------
1 Mr. john tiger
2 kim wood
3 Mrs. woodside

Then, I created a view like this:
CREATE VIEW EMP_VIEW AS
SELECT EMPID,
EMPTITLE + '' +EMPFIRST+''+EMPLAST AS EMP_NAME
FROM EMP

ONLY 1 record shows at Emp=1, the others shows NULL.
The values return from View that I want is like this:
Mr. John Tiger
Kim Wood
Mrs Woodside

Can you please show me how to do it?

Thank you very much,
Jen.

The stupid question is the question you don't ask.
www.single123.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-28 : 13:40:33

You need to turn CONCAT_NULL_YIELDS_NULL option off:

SET CONCAT_NULL_YIELDS_NULL OFF

Tara

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-28 : 13:54:27

Thanks, so much, Tara.
Jen.

The stupid question is the question you don't ask.
www.single123.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 13:56:33

Alternatively:

CREATE VIEW EMP_VIEW AS
SELECT EMPID
COALESCE(EMPTITLE,''), + ' ' +COALESCE(EMPFIRST,'')+' '+COALESCE(EMPLAST,'') AS EMP_NAME
FROM EMP

rockmoose

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-28 : 14:15:47

WOH WOH WOH.... ROCK, YOURS IS BETTER 'CAUSE I DON'T HAVE TO REMEMBER TURNING ON & OFF THAT THING....
I MODIFED MY VIEW BASED ON YOUR SCRIPT.
I CAN USE EITHER COALESCE OR ISNULL, RIGHT? BUT I USE COALESCE.
I APPRECIATED YOUR HELP.
JENNY

The stupid question is the question you don't ask.
www.single123.com

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 07:26:55

COALESCE allows you to use more than two values, and is the ANSI standard name. Also I think that ISNULL is a useless name (sounds like its a TEST for NULL to me)

So you can do SELECT COALESCE(Col1, Col2, Col3, ..., ColN) and you will get the first non-NULL value in the list

Also you might want to do:

TRIM(COALESCE(EMPTITLE + ' ', '') + COALESCE(EMPFIRST + ' ', '') + COALESCE(EMPLAST,''))

so that you don't get extra spaces creeping in

Kristen

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-29 : 09:41:41

But if you are only replacing a null with 1 value Isnull is usually faster.

-Jon
Should still be a "Starting Member"

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-29 : 12:26:11

And the number 1 reason for not messing with ANY settings....

quote:
Originally posted by jennypretty

'CAUSE I DON'T HAVE TO REMEMBER TURNING ON & OFF THAT THING....

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:26:41

quote:
Originally posted by surefooted

But if you are only replacing a null with 1 value Isnull is usally faster

?
Please elaborate Jon, I never heard of that before..
......
I did hear though that COALESCE is SQL Standard nad ISNULL is not,
anyone know about thatfor sure ?

lazy moose that doesn't browse the sql standard papers,
rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:32:32

quote:
Originally posted by X002548

And the number 1 reason for not messing with ANY settings....

quote:
Originally posted by jennypretty

'CAUSE I DON'T HAVE TO REMEMBER TURNING ON & OFF THAT THING....

Brett

8-)

Brett, have you tried...
SET NOCOUNT OFF !?
SET QUOTED_IDENTIFIER ON !?

Agree though, I never mess with settings stuff, at least I don't rely on settings stuff to keep things working.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 12:34:19

"I did hear though that COALESCE is SQL Standard nad ISNULL is not, anyone know about thatfor sure ?"

I read that here too ... <g>

ISO/IEC 9075-2:1999
6.21 <case expression>

2) COALESCE (V 1 , V 2 ) is equivalent to the following <case specification>:

CASE WHEN V 1 IS NOT NULL THEN V 1 ELSE V 2 END

3) ‘‘COALESCE (V 1 , V 2 ,...,Vn)’’, for n >= 3, is equivalent to the following <case specification>:

CASE WHEN V 1 IS NOT NULL THEN V 1 ELSE COALESCE (V 2 ,...,V n ) END

Can't find the word ISNULL anywhere in that document

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-29 : 12:45:35

NOCOUNT Doesn't count

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:55:15

SET MOUTH_SHUT OFF

Thanx Kristen, now I can sleep again. ( prefer COALESCE anyway btw )
Brett, NOCOUNT doesn't count, How could I miss that !?!

SET MOUTH_SHUT ON

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 13:24:38

Is this related to the

OPEN MOUTH
INSERT FOOT
ECHO WORLDWIDE

thread?!

Kristen

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-29 : 13:37:34

Rockmoose, Isnull is a transact sql extension. Sorry I didn't get back earlier. Kristen said it though so thanks.

-Jon
Should still be a "Starting Member"

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-29 : 13:56:36

quote:
Originally posted by Kristen

Is this related to the

OPEN MOUTH
INSERT FOOT
ECHO WORLDWIDE

thread?!

Kristen

You forgot to close and deallocate

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 18:53:14

Then I guess I'm still echoing worldwide !

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources