How to split on a double quote

• budke

  Grasshopper

  Points: 16

  More actions

  February 12, 2009 at 8:48 am

  #201071

  I've got lines in the db like

  UserLogon Logon "Domain\Username" from blah

  UserLogon Logon "Domain\Username2"

  I only want the data in the "'s, but can't figure out how to write the split or whatever which thinks the " is identifying text.

• Ken McKelvey

  SSCoach

  Points: 18844

  More actions

  February 12, 2009 at 9:23 am

  #942979

  -- *** Test Data ***

  DECLARE @t TABLE

  (

  &nbsp&nbsp&nbsp&nbspLine varchar(50) NOT NULL

  )

  INSERT INTO @t

  SELECT 'UserLogon Logon "Domain\Username" from blah' UNION ALL

  SELECT 'Junk' UNION ALL

  SELECT '"Junk2' UNION ALL

  SELECT 'UserLogon Logon "Domain\Username2"'

  -- *** End Test Data ***

  SELECT SUBSTRING(Line, StartPos, EndPos - StartPos) AS AccountName

  FROM

  (

  &nbsp&nbsp&nbsp&nbspSELECT *

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('"', Line, StartPos) AS EndPos

  &nbsp&nbsp&nbsp&nbspFROM

  &nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspLine

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('"', Line) + 1 AS StartPos

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM @t

  &nbsp&nbsp&nbsp&nbsp) D1

  &nbsp&nbsp&nbsp&nbspWHERE StartPos > 1

  ) D

  WHERE EndPos > 0