Home Forums SQL Server 2008 T-SQL (SS2K8) Help needed to decode comma delimited string and insert into tables RE: Help needed to decode comma delimited string and insert into tables

  • February 21, 2011 at 4:24 pm

    #1289649

    JJH82 (2/21/2011)

    I appologise for the troubles I caused.

    Here is a detailed structure of the source data:

    1. the length of each row of string is not fixed

    2. the data contained in the string is comma seperated and start with a comma,

    e.g. , a ,b ,c

    3. every 3 commas represent a set of information,

    e.g. , a1 , a2 , a3 , b1 , b2 , b3

    4. each section between 2 commas contains a set of fixed format string,

    e.g. ,1 New_Field "Field 1" , 1 New_Field_Name "New Field" , 1 Old_Field_Name "Old Field"

    5. there are 2 types of value changes the audit table captures, Field and Column .

    Field name may contain space, but column name is a whole string with "_",

    new and old value may contain space

    all data values are stored in "", empty value are represented by ""

    6. each row of record may contain both field and column changes, or it may contain column changes only

    7. some real data sample:

    ,1 Field_Name "Service Type Name" ,1 New_Field_Name "Disability" ,1 Old_Field_Name "" ,2 Column_Name "EFF_END_DATE" "EFF_START_DT" ,2 New_Column_Value "2014-10-31" "2010-10-01" ,2 Old_Column_Value "" ""

    ,2 Column_Name "X_ALLOW_CASE_CLAIMS" "X_CASE_TYPE" ,2 New_Column_Value "N" "Agreement Schedule" ,2 Old_Column_Value "" ""

    ,9 Column_Name "NAME" "TAX_IDEN_NUM" "X_GST_REG_FLG" "REL_NAME" "X_PAYMENT_METHOD" "X_PAYMENT_TERM" "CUST_STAT_CD" "NAME_1" "X_WITHHOLD_TAX_EXEMPT_FLG" ,9 New_Column_Value "ER15 ORG Test" "48123123124" "Y" "ER15 ORG Test" "None" "Immediate" "Draft" "ER15 ORG Test" "Y" ,9 Old_Column_Value "" "" "" "" "" "" "" "" ""

    ,1 Column_Name "ATTRIB_07" ,1 New_Column_Value "No" ,1 Old_Column_Value ""

    ,1 Column_Name "STATUS_CD" ,1 New_Column_Value "Submitted" ,1 Old_Column_Value "Draft"

    p.s. at the moment, I am trying the code provided by kumar20, it breaks when it hits space between string. I am working to overcome it now

    It's no trouble on our end. We're just trying to provide a solution. I missed the part about multiple fields being in a single section. 😛 Thank you for the update.

    I believe that kumar20's code is a step in the right direction. You may have to suffix each row with an extra space to get it to work for multiple values within a field selection.

    That, not withstanding, I'll take a look at a different method.

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