Post reply

I want to capture the data in a field ipto last comma.

  • September 28, 2010 at 8:29 am

    #238910

    Hi All,

    i have a table with a field as above.

    John,Doe,120 jefferson st.,Riverside, NJ, 08075

    Jack,McGinnis,220 hobo Av.,Phila, PA,09119

    "John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075

    I want the data to look like below.

    John,Doe,120 jefferson st.,Riverside, NJ

    Jack,McGinnis,220 hobo Av.,Phila, PA

    "John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ

    That means, i want to capture the data only upto the last comma.

    How can this be done?

    Please guide me..

    Thanks,

    Sahasam

  • September 28, 2010 at 8:39 am

    #1228420

    you'll want to use a combination of tw REVERSE commands,SUBSTRING and a CHARINDEX to get at what you want;

    this kind of breaks it down into understandable pieces(i hope)

    with myExampleCTE as

    (SELECT 'John,Doe,120 jefferson st.,Riverside, NJ, 08075' As TheAddress UNION ALL

    SELECT 'Jack,McGinnis,220 hobo Av.,Phila, PA,09119' UNION ALL

    SELECT '"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075'

    ),

    MyReversedData as (SELECT REVERSE(TheAddress) AS REV FROM myExampleCTE

    )

    SELECT

    REVERSE(REV) As TheReversedString,

    CHARINDEX(',',REV) As TheCommaLocation,

    SUBSTRING(REV,CHARINDEX(',',REV) + 1,50) as IntermediateResults,

    REVERSE(SUBSTRING(REV,CHARINDEX(',',REV) + 1,50)) As DesiredResults

    FROM MyReversedData

    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, 2010 at 9:49 am

    #1228493

    Thanks Lowell..That helps..

  • September 28, 2010 at 4:31 pm

    #1228711

    I think there may be a slight problem Lowell with your SUBSTRING not being long enough for the last one.

    Another variation

    SELECT LEFT(Address, LEN(Address) - CHARINDEX(',', REVERSE(RTRIM(Address))))

    FROM

    (

    VALUES

    ('John,Doe,120 jefferson st.,Riverside, NJ, 08075'),

    ('Jack,McGinnis,220 hobo Av.,Phila, PA,09119'),

    ('"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075')

    ) AS Z (Address)

Viewing 4 posts - 1 through 3 (of 3 total)

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