Parsing String Values

• monkster

  SSC Journeyman

  Points: 75

  More actions

  November 3, 2004 at 7:00 am

  #85711

  I recently "inherited" an SQL Server database that was designed incorrectly. Actually it was dumped in my lap. My  question is about parsing a string value. Here is an example...

  There is a Field named Description in an inventory database. The user was required to enter 3 statements describing a part. Example would be "Screw, 1/4 inch diamerter, 1 inch long". The users all want the capability of sorting by each of the 3 descriptions. So I decided to break "Description"into 3 fields - "PartDescription", "Diameter", and "Length".

  My question is how do I parse the original string value into 3 fields. The 3 descriptions are in MOST cases, seperated by a comma.

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 3, 2004 at 7:35 am

  #528775

  See if this helps

  declare @a varchar(40)

  set @a = 'Screw, 1/4 inch diamerter, 1 inch long'

  set @a = replace(@a,',','.')

  select

  ltrim(rtrim(parsename(@a,3)))

  ,ltrim(rtrim(parsename(@a,2)))

  ,ltrim(rtrim(parsename(@a,1)))

  This will leave some clean up work for you to do.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]