Post reply

Removing Commas in derived Column?

  • January 23, 2012 at 5:38 am

    #391373

    Hi Friends,

    i have a column having some records like ",Clayton" and "Township," and "Clayton, Dept" ...

    i want to remove unnecessary commas which is presented in the 1st two records...

    how to get a derived column condition for this?

    Thanks in advance

    Thanks,
    Charmer

  • January 23, 2012 at 6:41 am

    #1437055

    Charmer (1/23/2012)

    Hi Friends,

    i have a column having some records like ",Clayton" and "Township," and "Clayton, Dept" ...

    i want to remove unnecessary commas which is presented in the 1st two records...

    how to get a derived column condition for this?

    Thanks in advance

    Do the "records" also contain the double-quotes or are they just there to show grouping for your example?

    --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)
    Intro to Tally Tables and Functions

  • January 23, 2012 at 6:53 am

    #1437058

    Jeff Moden (1/23/2012)

    Charmer (1/23/2012)

    Hi Friends,

    i have a column having some records like ",Clayton" and "Township," and "Clayton, Dept" ...

    i want to remove unnecessary commas which is presented in the 1st two records...

    how to get a derived column condition for this?

    Thanks in advance

    Do the "records" also contain the double-quotes or are they just there to show grouping for your example?

    no double quotes...just for example..

    Thanks,
    Charmer

  • January 23, 2012 at 7:12 am

    #1437081

    I am trying to guess the reason for Jeff’s question. I received few csv files from customer couple of day’s back, that had similar structure. It was a result of bad data export. Thus [“] double quotes used to appear in columns (first & last only) as well as rows.

    Now, for your question, what do you mean by ‘just for example’? Are you just playing (R&D) with csv with different formats?

  • January 23, 2012 at 7:21 am

    #1437091

    Dev (1/23/2012)

    I am trying to guess the reason for Jeff’s question. I received few csv files from customer couple of day’s back, that had similar structure. It was a result of bad data export. Thus [“] double quotes used to appear in columns (first & last only) as well as rows.

    Now, for your question, what do you mean by ‘just for example’? Are you just playing (R&D) with csv with different formats?

    its not R&D..i am having .xls file contains the data's (no quotes) like the above i said....

    Thanks,
    Charmer

  • January 23, 2012 at 7:40 am

    #1437102

    If it’s in the record, you should accept it as it is. Isn’t it valid data in business terms?

    For example, a csv or xls can have a memo / notes column where you may find many commas in text. Its valid record & we shouldn’t temper it in data load.

  • January 24, 2012 at 2:14 am

    #1437512

    simply REPLACE([FIELD NAME],',','')

  • January 25, 2012 at 2:29 am

    #1438140

    Hello,

    try it:

    WITH CTE(x) AS (

    SELECT ', Clayton' UNION SELECT 'Township,' UNION SELECT 'Clayton, Dept')

    , NoRightComma (x) AS (

    SELECT CASE RIGHT(x, 1) WHEN ',' THEN LEFT(x, LEN(x) - 1) ELSE x END

    FROM CTE

    )

    SELECT CASE LEFT(x, 1) WHEN ',' THEN LTRIM(SUBSTRING(x, 2, LEN(x))) ELSE x END

    FROM NoRightComma

    Have you an Excel file as data source?, I think it is best to correct it before load it into SQL Server as a matter of input quality, that's my point of view. If this field is the output of an Excel formula you should simply correct this formula at source avoiding bad code in next steps.

    Regards,

    Francesc

  • January 25, 2012 at 6:16 am

    #1438189

    phil.walter 85406 (1/24/2012)

    simply REPLACE([FIELD NAME],',','')

    It won't work for the third example the OP gave where the comma between two items must be preserved.

    --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)
    Intro to Tally Tables and Functions

  • January 25, 2012 at 6:20 am

    #1438193

    Jeff Moden (1/25/2012)

    phil.walter 85406 (1/24/2012)

    simply REPLACE([FIELD NAME],',','')

    It won't work for the third example the OP gave where the comma between two items must be preserved.

    Thanks Guys for the ideas given

    Thanks,
    Charmer

  • January 25, 2012 at 6:19 pm

    #1438521

    Agh... My apologies. The reason I didn't offer up any code is because it would appear that Francesc has sussed this problem and I meant to say so.

    --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)
    Intro to Tally Tables and Functions

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

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