concatenate using a hard return? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

concatenate using a hard return?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-06-09 : 15:11:05

Can one use string concatenation to stack values in a cell, like so:

100
200

from BOL, I see SPACE(1) used, can I call for a hard return somehow?

SELECT (au_lname + ',' + SPACE(1) + SUBSTRING(au_fname, 1, 1) + '.') AS Name

thx

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-09 : 15:16:00

Can do, but why?

select '0 ' + char(13) + char(10) + '1'

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 15:17:36

damn: sniped

DECLARE @au_lname varchar(30), @au_fname varchar(30)
SELECT @au_lname = 'Kaiser', @au_fname = 'Brett'

SELECT @au_lname + CHAR(13)+CHAR(10) + @au_fname AS Name

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-06-09 : 15:23:30

thanks all.

this output goes to excel, the reason for the stacked format is that the data is more meaningful when displayed this way.

J

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-06-09 : 16:28:44

anyone know how to eliminate the vertical bars once the data is transferred to excel. data looks fine, I just want to search for the vertical bar and replace it with nothing.

thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-09 : 16:30:18

How about Replace function in the Edit menu? You can also get to it with Ctrl+H.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 16:35:20

quote:
Originally posted by steelkilt

anyone know how to eliminate the vertical bars once the data is transferred to excel. data looks fine, I just want to search for the vertical bar and replace it with nothing.

thx

Did you pick it as a delimeter?

How did you transfer the data?

I'm assuming DTS...what object did you pick?

Excel, text file?

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-06-09 : 16:56:34

didn't pick it as a delimiter

transferred using DTS to to Excel 8.0

thx

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 17:01:12

CReATE VIEW myView99
AS
SELECT Col1, REPLACE(Col2,'|',''), Col3, ect

DTS out the view

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources