Author |
Topic |
RoniDxb
Starting Member
28 Posts |
Posted - 2008-08-04 : 19:05:09
|
My tablefName.............lName...........................................Nancy.............Davolio............Andrew............Fuller.............Janet.............Leverling..........Margaret..........Null...............Null..............Robert............Plz guide me how can i concatinate two columns of a table for example in able tableThere are two col in MyTable, i want to concatinate them as full Name likeFull NameNancy DavolioAndrew FullerJanetLeverlingMargaret Robertsecondly how can we give more than one spaces while writting topic.i have seen when we give spaces, after topic is posted, all spaces are vanished. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
RoniDxb
Starting Member
28 Posts |
Posted - 2008-08-04 : 19:10:58
|
ThanksI was writting fName & '' & lName instead of that. |
 |
|
RoniDxb
Starting Member
28 Posts |
Posted - 2008-08-04 : 19:32:10
|
I have tried the above method it runs successfully only on those rows which have no null value.if any of two col of a row has a null value, result be null for examplefName............lName............ resultMargaret..........Null.............NullNull..............Robert............Nulli want it should not show null if we have fName or lName value |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-05 : 13:42:34
|
quote: Originally posted by RoniDxb I have tried the above method it runs successfully only on those rows which have no null value.if any of two col of a row has a null value, result be null for examplefName............lName............ resultMargaret..........Null.............NullNull..............Robert............Nulli want it should not show null if we have fName or lName value
SELECT COALESCE(fName + ' ','') + COALESCE(lName,'')FROM Mytable |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-05 : 14:04:00
|
Umm, okay. We aren't helping them learn if we just keep giving them the entire thing. I provided enough information in my posts to come up with the solution.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
RoniDxb
Starting Member
28 Posts |
Posted - 2008-08-05 : 14:32:48
|
Yes thanks tKizer i tried IsnUll function yesterday and i solved my problem.The solution that Visakh has just put, i have also chked now.COALESCE function is too useful . i have also note some difference between COALESCE and IsNull.I have checked the COALESCE function in online books but i could not understand COALESCE parameters.can u or anyone help me to understand the parameters of COALESCE |
 |
|
veerakarthi
Starting Member
3 Posts |
Posted - 2008-08-06 : 02:27:34
|
COALESCE function can have any number of parameters, But it should be of same data type or implicit conversion should be possible.This function will return the first non null value from the given parameters.For ex: SELECT COALESCE('a', NULL, 'b', NULL)will give the result as 'a'SELECT COALESCE(NULL, NULL, 'b', 'a' , '')will give the result as 'b' |
 |
|
|