How to skip null value during concatination of string.(Urgent)

Question

How to skip null value during concatination of string.(Urgent)

@nks

Mr or Mrs. 500

Points: 500
More actions
November 7, 2008 at 2:30 am

#92817

I have following fields in database. i.e. ID,firstname,middlename,lastname,address,phoneno etc.
I want to concat firstname,middlename,lastname as fullname
select ID,firstname+' '+middlename+' '+lastname, address,phoneno from contact
but my problem is if one of the field firstname, middlename,lastname is null then the it returns the fullname as null .
so how to overcome this problem .
if any of the field fistname,middlename,lastname
is null then simply skip that field and concat remaining fields,
or how to check each field whether it is null or not at query time using conditional statements.
Thanks and regards.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

try like this:

select ID, isnull (firstname, '') + ' ' + isnull (middlename, '') + ' ' + isnull (lastname, ''), isnull (address, ''), isnull (phoneno, '') from contact

@nks Mr or Mrs. 500 Points: 500 More actions · Answer 2

Thanks, It is working.

Now my question is, whether I can check ISNotNull(x,y) or any other solution is available.

Thanks & Regards

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]

ankur (11/7/2008)
Thanks, It is working.
Now my question is, whether I can check ISNotNull(x,y) or any other solution is available.
Thanks & Regards

Here, if you wants to return Y in case of X is null the use COALESCE. Before let me know whether I guess right?

Mahesh[/font]

MH-09-AM-8694

@nks Mr or Mrs. 500 Points: 500 More actions · Answer 4

@nks

Mr or Mrs. 500

Points: 500

November 8, 2008 at 9:47 pm

#896015

I mean to say that if x is NOT NULL then y.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

ankur (11/8/2008)
I mean to say that if x is NOT NULL then y.

Like this:

(CASE WHEN X IS NOT NULL THEN Y ELSE NULL END)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

mmeausa-adios Valued Member Points: 73 More actions · Answer 6

Kishore.P said:

try like this:

select ID, isnull (firstname, '') + ' ' + isnull (middlename, '') + ' ' + isnull (lastname, ''), isnull (address, ''), isnull (phoneno, '') from contact

This is close to my solution:

[font="Courier New"]SELECT ID, RTRIM(ISNULL(firstname + ' ', '') + ISNULL(middlename + ' ', '') + ISNULL(lastname, '')), ISNULL(address, ''), ISNULL(phoneno, '') from contact [/font]

By concatenating the space (' ') inside the ISNULL call, you will prevent back to back spaces. (If [font="Courier New"]FirstName [/font]is null, then [font="Courier New"]FirstName + ' '[/font] is also null)

The RTRIM will remove the trailing space, in the event that LastName is null.

Cheers!