Query

Question

Post reply

Query

The_SQL_DBA

SSChampion

Points: 10494
More actions
September 25, 2008 at 12:55 pm

#194151

How can I change the result in the Expr2 to appear as CR and not ~~ that has entered some how into the table wrongly.
The query I use is
SELECT TblC1.Client AS Expr1, TblC2.strAddr AS Expr2
FROM TblC2 INNER JOIN
TblC1 ON TblC2.IndCnt = TblC1.IndCnt
WHERE (TblC1.Client = 1209)
this returns something like
expr1 expr2
1209 11 Trafalgar Sq Ste 101~~WS7216
1209 25 Hanover Street
How can I change the ~~ to appear as CR..
Thanks
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

are you looking fto simply REPLACE of ~~ with CR? or is it more complex than that?

SELECT TblC1.Client AS Expr1, REPLACE(TblC2.strAddr,'~~','CR') AS Expr2

FROM TblC2 INNER JOIN

TblC1 ON TblC2.IndCnt = TblC1.IndCnt

WHERE (TblC1.Client = 1209)

The_SQL_DBA (9/25/2008)
How can I change the result in the Expr2 to appear as CR and not ~~ that has entered some how into the table wrongly.
The query I use is
SELECT TblC1.Client AS Expr1, TblC2.strAddr AS Expr2
FROM TblC2 INNER JOIN
TblC1 ON TblC2.IndCnt = TblC1.IndCnt
WHERE (TblC1.Client = 1209)
this returns something like
expr1 expr2
1209 11 Trafalgar Sq Ste 101~~WS7216
1209 25 Hanover Street
How can I change the ~~ to appear as CR..
Thanks

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

you should have post this query into DEVELOPMENT or TSQL 2K5 folder.this is related to admnistration part

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

HanShi SSC-Dedicated Points: 33508 More actions · Answer 3

If you mean a 'Carriage Return' by CR then look at the "char" function: char(10) = linefeed, char(13) = carriage return.

Replace the '~~' with the technique Lowell showed in a previous reply.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Lowell SSC Guru Points: 323518 More actions · Answer 4

doh i didn't even see that CR might mean Carriage Return Line Feed...goodjob...

Like Hanshi said, you might want something like

REPLACE(TblC2.strAddr,'~~',CHAR(13) + CHAR(10)) as your expression

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

Sorry about not posting in detail. The export job was failing on account of seeing some alien char. It's a CHAR RERTURN...I got it now..appreciate all your replies guys..thanks!!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."