Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Unanswered: Nested CASE to parse a string

    I am attempting to create a view to bring together multiple joined tables based ona one to many relationship

    Eg Table1 contains code, address
    Table2 contains code, financial details
    table3 contains code, financial history

    This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2

    My current stumbling block is:


    One of the fields in table1 is a free format address field (address).
    eg. could be (street addres, city prov, postal)
    or
    could be (street address 1, address2, address 3, city prov, postal)

    I want to be able to assign individual components of the address to corresponding fields

    if # of commas = 2 then
    address1 = substring(address,1, position of first comma)
    cityprov = substring(address,possition of first comma, position of second comman)
    postal = substring(address rest of field)

    I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...

    ANy ideas on how best to accompish this?
    ( this table is needed for some leacy software which can only handle one record with all infor....

    greg

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    From your description, it appears you are using a cursor to try and parse the data column from Table1.

    Using charindex(), a couple of integer variables, and a few string variables, you should be able to parse out the Table1 information into x number of rows based on the number of commas in the string.

    If this is true, then go lookup charindex() in Books OnLine. See if you can figure out your methodology from there. Come back if you have questions.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    1st post did not update to show response. This should do it!

    -- This is all just a Figment of my Imagination --

Posting Permissions

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