function required to separate a concatenated string

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  February 13, 2008 at 7:25 am

  #122029

  Hi,

  I have an address column (Address) that is the result of 4 concatenated fields with '/N' or '/n' separators.

  where '/N' is the 1st separator and '/n' is the 2nd and 3rd separator.

  For example:

  MANOR HOUSE /NSECOND GREENHOW /nGREAT AYTON /nMIDDLESBOROUGH

  In some instances there are not values after the separators but the separators will still exist:

  MR BANKS /NWOODSTON FARM /nKINROSS /n

  Each portion is nvarchar(30) without the '/N' or '/n'.

  I need to divide the column into 4 fields (Add1; Add2; Add3; Add4). Some fields will obviously be 'blank' or 'NULL'.

  I know how to Substring(Address, 1, 30) etc - But is this the only way?

  Thanks in advance

  Neal

• Frank Bazan

  SSCertifiable

  Points: 5378

  More actions

  February 13, 2008 at 7:34 am

  #777817

  I don't think you'll need a function for this... Try making /N the column delimiter in your flat file.

  Kindest Regards,

  Frank Bazan