Left of a comma

• Richard Sabbara

  Ten Centuries

  Points: 1382

  More actions

  August 10, 2006 at 7:21 am

  #173669

  I have a field in a table that is a last name, first name (ID), I can parse this out in EXCEL in my sleep not a problem, but I want to be able to do this SQL since I will be bumping this up agianst another table to get another field.

  Any suggestions?

  Richard

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 10, 2006 at 7:38 am

  #654299

  Richard

  Look up the CHARINDEX, LEFT and RIGHT functions in Books Online.  They should help you get what you want.

  John

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  August 10, 2006 at 11:06 am

  #654383

  declare @t table(namestr varchar(50) not null)

  insert @t

  select 'Aardvark, Archie (1)' union all

  select 'Baboon, Bob (2)' union all

  select 'Chimp, Charlie (3)'

  select ltrim(rtrim(left(namestr, commaPos - 1))) as LastName

   ,ltrim(rtrim(substring(namestr, commaPos + 1, bracket1Pos - commaPos - 1))) as Firstname

   ,cast(substring(namestr, bracket1Pos + 1, bracket2Pos - bracket1Pos - 1) as int) NameID

  from (

   select namestr

    ,charindex(',', namestr) as commaPos

    ,charindex('(', namestr) as bracket1Pos

    ,charindex(')', namestr) as bracket2Pos

   from @t ) D