How can we concatinate two cols of a table.

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)

How can we concatinate two cols of a table.

Author

Topic

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-04 : 19:05:09

My table

fName.............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 table
There are two col in MyTable, i want to concatinate them as full Name like

Full Name

Nancy Davolio
Andrew Fuller
JanetLeverling
Margaret
Robert

secondly 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

Posted - 2008-08-04 : 19:07:48

SELECT fName + ' ' + lName
FROM Mytable

Use code tags to retain code format and data format in these posts.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-04 : 19:10:58

Thanks
I 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 example
fName............lName............ result

Margaret..........Null.............Null
Null..............Robert............Null

i want it should not show null if we have fName or lName value

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 19:35:10

You can use COALESCE or ISNULL functions for those scenarios.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

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 example
fName............lName............ result

Margaret..........Null.............Null
Null..............Robert............Null

i 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources