Post reply

Query

  • 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."

  • September 25, 2008 at 1:20 pm

    #877300

    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!

  • September 26, 2008 at 2:53 am

    #877492

    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;-)

  • September 26, 2008 at 6:04 am

    #877579

    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’! **

  • September 26, 2008 at 7:42 am

    #877686

    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!

  • September 28, 2008 at 7:51 am

    #878165

    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."

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

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