Jioning Text fields together in SQL

Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.
a_blah.blah+CHAR(13)+b_blah.blah
Thanks for your help.That seems right, unless that is part of your result set, then you'll need an "AS" clause:

(table1.value1 + CHAR(13) + table2.value2) AS CombinedValue

Depending upon the datatype of value1 an value2, you need to CONVERT or CAST first.|||Thanks for the reply...

That is pretty much what I'm doing with the AS command, but I get an error when I try to run the query... can't find prog.char(13)??

I'm confused.|||This is straight out of the BOL:

USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1

When I run it, it works fine. Can you use CHAR for a normal alphabetic character? CHAR(65) = A|||It didn't work on mine?? This is the actual code that I'm trying to run... I do appreciate your help.

SELECT a_psupp.comp_no, a_psupp.comp_name AS 'Supplier Code',

a_psupp.add1 + CHAR(13) + a_psupp.add2 + CHAR(13) +
a_psupp.add3 + CHAR(13) + a_psupp.add4 + CHAR(13) +
a_psupp.add5 AS 'Ordering Address',

a_psupp.tel AS 'Telephone Number',
a_psupp.fax AS 'Fax Number',
a_psupp.comm1+" "+a_psupp.comm2 AS 'Supplier Notes'

FROM a_psupp a_psupp

ORDER BY a_psupp.comp_no

When this runs i get the error Can't Find Char.PRG

Thanks|||This is straight out of the BOL:
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
if that's straight out of BOL, it has syntax errors!!!

it is followed by:Here is the result set:Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle WANote: In this record, the data in the Address column also contains a control character.
that's gotta be the stupidest example i've seen in a long time

"WA" stored in the Region column? i don't have northwind, so i can't check

and where did the spacing come from?

tsk, where was basic QA when this was written...|||if that's straight out of BOL, it has syntax errors!!!

tsk, where was basic QA when this was written...
I thought the same, but I didn't want to doctor the BOL if I was going to quote it. It still does run, but I agree.

a_psupp.comm1+" "+a_psupp.comm2 AS 'Supplier Notes'

cfeltham, are those double quotes in your query? Replace them with single quotes and retry.|||The double quotes might be hosing things up. On a side note, you should check for NULL values when concatenating text strings, as the concatenation of a NULL value with a string value normally returns NULL (Unless you specifically change the CONCAT_NULL_YIELDS_NULL setting).

SELECT a_psupp.comp_no,
a_psupp.comp_name AS 'Supplier Code',
isnull(a_psupp.add1, '') + CHAR(13)
+ isnull(a_psupp.add2, '') + CHAR(13)
+ isnull(a_psupp.add3, '') + CHAR(13)
+ isnull(a_psupp.add4, '') + CHAR(13)
+ isnull(a_psupp.add5, '') AS 'Ordering Address',
a_psupp.tel AS 'Telephone Number',
a_psupp.fax AS 'Fax Number',
isnull(a_psupp.comm1 + ' ', '') + isnull(a_psupp.comm2, '') AS 'Supplier Notes'
FROM a_psupp
ORDER BY a_psupp.comp_no|||I thought the same, but I didn't want to doctor the BOL if I was going to quote it.sir/madam, i quite admire your standards

It still does run...

wtf? i don't believe it

omg, you are right, you can actually start an expression with a concatenation operator
create table concatstrings
( id smallint not null identity primary key
, foo varchar(9)
, bar varchar(9)
, qux carchar(9)
)

insert into timediffs ( foo,bar,qux ) values ( 'the','three','stooges' )
insert into timediffs ( foo,bar,qux ) values ( 'curly','larry','moe' )

select foo + ' ' + bar
, + CHAR(13) + qux
, + id
from concatstrings

the three
stooges 1
curly larry
moe 2|||sir/madam, i quite admire your standards

wtf? i don't believe it

omg, you are right, you can actually start an expression with a concatenation operator

sir will be fine, and thank you :) I'm just a humble programmer, not a DBA, so I didn't want to insert my foot in my mouth and point out the possible error if there was a reason for it that I was unaware. Stranger things have happened...

INSERT INTO Mouth (LeftFoot, RightFoot) VALUES (1,1)

:)|||way too freaky
fyi
try the ascii() function
it's the opposite of char()|||try the ascii() function
it's the opposite of char()So are you suggesting that he insert his ASCII into his mouth ?!?!

-PatP|||you betcha, sport.|||Eeewwww!

-PatP|||...and the checks in the mail...|||BAH DUH BANG...BAH DUH BOOM

This such an education thread. :)|||you are so right

the thing i learned is that you can start an expression with a concatenation operator

select + foo from bar|||What another undocumented "Feature"

SELECT LEN(+' '), LEN(+ CustomerId), LEN(CustomerId) FROM Orders|||They could even be consecutive:

SELECT LEN(+++++' '), LEN(+++++ CustomerId), LEN(CustomerId) FROM Orders

There's a bit of trivia!|||tee hee

this is fun

select ' ' as space5
, len(' ') lenspace5
, ' '+'.' as space5dot
, len(' '+'.') as lenspace5dot|||alright you!!!!

select Char(79)+
Char(75)+
Char(33)
+ ' ' +
Char(67)+
Char(117)+
Char(116)
+ ' ' +
Char(105)+
Char(116)
+ ' ' +
Char(79)+
Char(117)+
Char(116)+
Char(46) as 'Hey You Guys!'

Take That!!!