Post reply

SQL STATEMENT advise please

  • December 4, 2007 at 7:47 am

    #184577

    pulling from table where it outputs for example

    245 High Street\

    or

    245 High Street\P.O. Box 12345

    I am wanting to find the \ slash and erase everthing from that point forward.

    where when I select the table info I get only

    245 high Street

    Thanks

  • December 4, 2007 at 7:57 am

    #756952

    here is the SQL I am running...

    Select personid,personnum,firstnm,lastnm,middleinitialnm,workemailaddress,

    companyhiredtm,birthdtm,homestreet,homecity,homestate,homezip,employmentstatus,employmentstatusdt

    from VP_personv42

    where Employmentstatusdt = convert(char(12),GETDATE()-1,101)

    OR employmentstatus = 'active'

    order by personnum

    The field in question is Homestreet which is actually pulled from another table called POSTALADDRESS. I had tried the following but this actually puts a additional space in the table.. really trying to TRUNCATE the field starting at the \ slash

    update postaladdress set streetaddresstxt = replace (streetaddresstxt, '\', ' ') from postaladdress

    I need to update the postaladdress table but want to delete everything starting at the \ slash!

    Thanks..

  • December 4, 2007 at 8:04 am

    #756958

    UPDATE postaladdress SET streetaddresstxt = SUBSTRING( streetaddresstxt, 1, CHARINDEX( '\', streetaddresstxt ) )

    FROM postaladdress

    WHERECHARINDEX( '\', streetaddresstxt ) > 0

    [/code]

    --Ramesh

  • December 4, 2007 at 8:55 am

    #756994

    thanks!!! Will give that a try.

  • December 4, 2007 at 9:07 am

    #757005

    I get incorrect syntex near '\'

    thoughts?

  • December 4, 2007 at 1:40 pm

    #757124

    Spaces sometimes get "eaten" when you cut and paste off of this board. Just mast sure to put a space in front of the word WHERE in the SQL Ramesh posted.:)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • December 4, 2007 at 2:00 pm

    #757133

    I'm no SQL expert, but from being a programmer it looks to me like it's a problem where the parser is intrepreting the backslash as the beginning of an escape character sequence. You probably need to specify the backslash in a special way.

  • December 4, 2007 at 7:02 pm

    #757192

    [font="Courier New"]DECLARE @PostalAddress TABLE (StreetAddressTxt VARCHAR(100))

    INSERT INTO @PostalAddress (StreetAddressTxt)

    SELECT '123456\78910' UNION ALL

    SELECT 'ABCDEFG\HIJKLM'

    SELECT * FROM @PostalAddress

    UPDATE @PostalAddress

    SET StreetAddressTxt = SUBSTRING( StreetAddressTxt, 1, CHARINDEX( '\', StreetAddressTxt )-1 )

    FROM @PostalAddress

    WHERE CHARINDEX( '\', streetaddresstxt ) > 0

    SELECT * FROM @PostalAddress[/font]

    No... Ramesh's code is good except the -1 needs to be added. And, no, the "\" is not being incorrectly interpreted. It's probably a copy error like Matt said... gotta look at the code before you try running it... space before the WHERE is eaten by the copy...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 4, 2007 at 11:07 pm

    #757216

    Good caught, jeff:D

    --Ramesh

  • December 5, 2007 at 11:10 am

    #757408

    thanks for the info.. will give it a try with my DB.

    Thanks again

    RM

Viewing 10 posts - 1 through 9 (of 9 total)

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