Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9

    Exclamation Unanswered: Desparately need help parsing field

    Hello,

    I have to split data that is currently in a single field delimited by a space.

    Problem is the field could be between 7 and 72 bytes long.
    I have to parse the field for a character set and its corresponding number.
    For instance: MyColumn = Index1 50 Index2 40 Index3 10

    I need to parse this out to:

    INDEX PERCENT
    ----------------------
    Index1 50
    Index2 40
    Index3 10

    Again, there may exist 1-4 sets of this data within the field.

    Anyone have a stored proc or better T-SQL ?

    Thanks so much in advance!

    mzimm

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Is this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?

  3. #3
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9
    Originally posted by rnealejr
    Is this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?
    Hello,

    This will repeat many times.
    Upwards of 1000 records exis t in this table.
    The datasets will be in combination of
    (Example) Index1 5 Index2 10 ... Indexn ##
    where Indexn can be any number of characters long and ## is between 1 and 100. The characters and numbers are separated by a space.

    Actual example:

    Column1 = Lehman 100
    Column2 = lehIUSGV 5 LehGVcr 10 MSCIEAFE 5 R2000Gro 5 RMCAPGro 10
    Column3 = LehCredt 10 Lehmktgov 15 DnB 35 S&P 40


    Thanks much for your help!

    mzimm

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?

  5. #5
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9
    Originally posted by rnealejr
    These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?
    I GREATLY aplogize, it's late.
    Those are RECORDS, only 1 column.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.

  7. #7
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9
    Originally posted by rnealejr
    This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.

    Yes, Please, can you post the VBscript?
    It would be thee answer to my problem.

    Thanks again.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Here you go (Let me know if you need additional help):

    Function Main()
    strString = DTSSource("b")
    intTest = 1
    intBegin = 1
    while intTest <> 0
    intTest = instr(intTest , strString, " ")
    strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & " "
    intTest = intTest + 1
    intBegin = intTest
    intTest = instr(intTest, strString, " ")
    if intTest = 0 then
    strFinal = strFinal & right(strString, len(strString) - intBegin + 1) & " "
    else
    strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & vbCrLf
    intTest = intTest + 1
    intBegin = intTest
    end if
    wend
    DTSDestination("b") = strFinal
    Main = DTSTransformStat_OK
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •