May 4, 2012 at 9:03 am
This is the table, where I would like to update with NULL values on the Lastname column with other columns.
Lastname Fname Lname Mname
Bannana,apple Mapple Bannana M
NULLNULLOrange NULL
NULLMangoLemon NULL
NULLNULL NULL NULL
I am trying to update the Lastname of the following table with "Lname,Fname,M". I am able to update onlastname easily if there is no NULL value on the column. If I have NULL value it is not updating. I am trying with the following script. Can you please suggest me a solution where the lastname gets updated by ignoring NULL Values.
Scripts worked on:
update [Tablename]
set LastName = lname + ',' + ' '+fname +' '+ mname
update Lname
set Lastname = case when Fname like '%null' then ' '
when Lname like '%null' then ' '
when Mname like '%null' then ' '
else lname + ',' + ' '+fname +' ' +mname
end
I am looking for an output like this:
Lastname Fname Lname Mname
Bannana,apple Mapple Bannana M
Orange,NULLOrange NULL
Lemon,MangoMangoLemon NULL
NULLNULL NULL NULL
Please, help me with this solution. Thank you.
May 4, 2012 at 9:10 am
DBA_SQL (5/4/2012)
This is the table, where I would like to update with NULL values on the Lastname column with other columns.Lastname Fname Lname Mname
Bannana,apple Mapple Bannana M
NULLNULLOrange NULL
NULLMangoLemon NULL
NULLNULL NULL NULL
I am trying to update the Lastname of the following table with "Lname,Fname,M". I am able to update onlastname easily if there is no NULL value on the column. If I have NULL value it is not updating. I am trying with the following script. Can you please suggest me a solution where the lastname gets updated by ignoring NULL Values.
Scripts worked on:
update [Tablename]
set LastName = lname + ',' + ' '+fname +' '+ mname
update Lname
set Lastname = case when Fname like '%null' then ' '
when Lname like '%null' then ' '
when Mname like '%null' then ' '
else lname + ',' + ' '+fname +' ' +mname
end
I am looking for an output like this:
Lastname Fname Lname Mname
Bannana,apple Mapple Bannana M
Orange,NULLOrange NULL
Lemon,MangoMangoLemon NULL
NULLNULL NULL NULL
Please, help me with this solution. Thank you.
Not tested, written on the fly:
update [TableName] set
LastName = nullif(isnull(Lname + ',','') + isnull(Fname + ' ','') + isnull(Mname,''),'')
May 4, 2012 at 9:25 am
It works!!! Thanks for quick response.
May 4, 2012 at 9:38 am
I'd also suggest that you have a carefull look in books online at the reference material for
set concat_null_yields_null on
set concat_null_yields_null off
these change the default behaviour of string concatenation involving NULL values for the session you are using (no other sessions are affected)
but..... like i said - read it very carefully - there are things that you MUST NOT DO when concat_null_yields_null is set of OFF (like index defrags)
MVDBA
May 4, 2012 at 10:47 am
Will do thanx mike
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply