Remove substring of fixed length following character.

• mickey w

  Old Hand

  Points: 369

  More actions

  April 20, 2015 at 2:45 am

  #296144

  Hi

  For reasons I'll not bore you with I have a field which contains values like

  'A100,B200'

  'A100,Y123,C300'

  'A100,Y123,Y999,D400'

  I need to find a way to remove from the string the 'Y codes' i.e. the letter Y itself and the following 4 characters (if there is a trailing comma, 3 characters where Y code is final in list). There may be zero to over 20 Y codes in each string. The table has approx. 20 million rows.

  e.g.

  create table #temp1

  (ID int, code varchar(max))

  INSERT #temp1 ([ID],) VALUES (1,N'A100,Y123,B200')

  INSERT #temp1 ([ID],) VALUES (2,N'A100,Y123,Y999')

  INSERT #temp1 ([ID],) VALUES (3,N'A100,Y123,Y999,C300')

  INSERT #temp1 ([ID],) VALUES (4,N'A100,Y234,B200')

  INSERT #temp1 ([ID],) VALUES (5,N'A100,Y143,Y999')

  INSERT #temp1 ([ID],) VALUES (6,N'A100,Y134,B200,A100,Y134,B200,A100,Y134,B200,A100,Y124,B200,A100,Y123,B200,A100,Y234,B200,A100,Y234,B200,A100,Y134,B200,A100,Y124,B200')

  I've experimented with the STUFF function and can almost get the behaviour I want but recognise that there must be a better way to achieve this.

  select ID, code, stuff(code, charindex(',Y',code,1), 5,'') as remove_1_y,

  stuff(stuff(code, charindex(',Y',code,1), 5,''),charindex('Y',stuff(code, charindex(',Y',code,1), 5,''),1),5,'') as remove_2_y

  from #temp1

  Grateful for any suggestions please.

  Thanks

  Mickey

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  April 20, 2015 at 3:10 am

  #1791953

  Here's a different approach

  WITH RemovedY AS (

  SELECT t.ID,

  t.CODE,

  SUBSTRING(t.CODE,(s.number*5)+1,4) AS CODE2,

  s.number AS Pos

  FROM #temp1 t

  INNER JOIN master.dbo.spt_values s ON s.type='P'

  AND (s.number*5)+1 < LEN(t.CODE)

  AND SUBSTRING(t.CODE,(s.number*5)+1,1) <> 'Y')

  SELECT t.ID,

  STUFF((SELECT ',' + r.CODE2 AS "text()"

  FROM RemovedY r

  WHERE r.ID = t.ID

  ORDER BY r.Pos

  FOR XML PATH('')),1,1,'') AS CODE

  FROM RemovedY t

  GROUP BY t.ID;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• mickey w

  Old Hand

  Points: 369

  More actions

  April 20, 2015 at 3:57 am

  #1791961

  that's a great suggestion - thanks.