Sql command to combine 2 fields from 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

General SQL Server Forums

New to SQL Server Programming

Sql command to combine 2 fields from a table

Author

Topic

aspbhavani
Starting Member

3 Posts

Posted - 2009-08-26 : 17:30:22

Hi,

I need to combine 2 columns of a table.

One field -
Datatype - varchar(50)

Second field -
Datatype - varchar(200)

The first field is never null but second field can be null.

I need to put in the dropdown box the combination of onefield + space + secondfield.

I tried -

Select onefield + ' ' + secondfield as column_new from table_name

Iam getting all null values in column z as I have all null values in secondfield. Please help me to get the correct data.

Regards,
Bhavani

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-26 : 17:35:06

select ltrim(rtrim(isnull(onefield,'') + ' ' + isnull(secondefield,'')))

No, you're never too old to Yak'n'Roll if you're too young to die.

aspbhavani
Starting Member

3 Posts

Posted - 2009-08-26 : 20:09:43

quote:
Originally posted by webfred

select ltrim(rtrim(isnull(onefield,'') + ' ' + isnull(secondefield,'')))

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi,
Thanks for the reply. Can you please explain a little bit about the statement. From what I understand, the statement returns the columnone + space + columntwo even if columnone is null or column two is null. Correct me if iam wrong.

Regards,
Bhavani

aareynaga
Starting Member

6 Posts

Posted - 2009-08-26 : 23:57:57

That's correct i believe, someone correct me if I'm wrong as I am pretty much new to SQL...the ltrim and rtrim pretty much just takes empty space out ltrim is for left and rtrim is for right.
Exanple:
onefield = " test "

LTRIM(onefield) = "test "
RTRIM(onefield) = " test"
LTRIM(RTRIM(onefield)) = "test"

ES QUEUE ELL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-27 : 06:53:15

OK, I will explain a little bit.
If you want to concatenate strings null values will force the whole formula to return null. Hence, we will eliminate null values using isnull() function.

isnull(column,'') --> returns an empty string if column is null

so for example if colum1 is null and column2 is not null
isnull(columns1,'') + ' ' + isnull(column2,'') --> returns ' col2_value'
if column1 is not null and column2 is null the statement returns 'col1_value '

So we have to delete unwanted spaces using ltrim() for left side and rtrim() for right side.

Hope that helps.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

aspbhavani
Starting Member

3 Posts

Posted - 2009-08-27 : 10:58:43

Thank you very much. It was very clear to me now. Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-27 : 11:03:39

welcome