Isolating a list with pattern search

• sqldriver

  SSChampion

  Points: 10440

  More actions

  July 14, 2014 at 12:24 pm

  #309073

  Hi,

  I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with. Any advice would be much appreciated.

  Thanks

  WITH bug (BugID, BugComment) AS (

  SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'

  UNION ALL

  SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'

  UNION ALL

  SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

  UNION ALL

  SELECT 4, 'OH GOD I''M ON FIRE DB_001000 Plick'

  UNION ALL

  SELECT 5, 'Where is SQL? DB_001000, DB_001000, DB_001000 Quee'

  UNION ALL

  SELECT 6, 'Good morning DB_001000 DB_001000 DB_001000 Snick'

  UNION ALL

  SELECT 7, 'Good afternoon DB_001000 DB_001000 DB_001000 Whick'

  UNION ALL

  SELECT 8, 'Good evening DB_001000 DB_001000 Doc'

  UNION ALL

  SELECT 9, 'I know it''s 4am but... DB_001000, DB_001000 , DB_001000 , DB_001000 , DB_001000 Grumpy'

  UNION ALL

  SELECT 10, 'I know it''s the weekend but... DB_001000DB_001000 , DB_001000 , DB_001000 , DB_001000 , DB_001000 Happy'

  UNION ALL

  SELECT 11, 'I need this in 30 seconds. DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 Sleepy'

  UNION ALL

  SELECT 12, 'Does data have feelings? DB_001000 DB_001000 Bashful'

  UNION ALL

  SELECT 13, 'Now now now DB_001000 DB_001000 DB_001000 Sneezy'

  UNION ALL

  SELECT 14, 'Harumph DB_001000 Dopey'

  UNION ALL

  SELECT 15, 'So long DB_001000,DB_001000 ,DB_001000 Huckepack'

  UNION ALL

  SELECT 16, 'Farewell DB_001000 DB_001000 Naseweis'

  UNION ALL

  SELECT 17, 'Auf wiedersehen DB_001000 Packe'

  UNION ALL

  SELECT 18, 'Goodbye DB_001000 DB_001000 DB_001000 Pick'

  UNION ALL

  SELECT 19, 'Swing DB_001000 DB_001000 Puck'

  UNION ALL

  SELECT 20, 'Batter DB_001000 DB_001000 Purzelbaum'

  UNION ALL

  SELECT 21, 'Batter DB_001000 Rumpelbold'

  )

  SELECT BugID, BugComment, SUBSTRING(BugComment, PATINDEX('%DB[_][0-9][0-9][0-9][0-9]%', BugComment), 100)

  FROM bug

  ORDER BY BugID

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  July 14, 2014 at 1:05 pm

  #1729252

  Not sure specifically what you're looking for, maybe this can help:

  ;WITH

  cteTally10 AS (

  SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

  ),

  cteTally100 AS (

  SELECT 1 AS tally

  FROM cteTally10 c1

  CROSS JOIN cteTally10 c2

  ),

  cteTally10K AS (

  SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally

  FROM cteTally100 c1

  CROSS JOIN cteTally100 c2

  ),

  bug (BugID, BugComment) AS (

  SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'

  UNION ALL

  SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'

  UNION ALL

  SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

  UNION ALL

  SELECT 4, 'OH GOD I''M ON FIRE DB_001000 Plick'

  UNION ALL

  SELECT 5, 'Where is SQL? DB_001000, DB_001000, DB_001000 Quee'

  UNION ALL

  SELECT 6, 'Good morning DB_001000 DB_001000 DB_001000 Snick'

  UNION ALL

  SELECT 7, 'Good afternoon DB_001000 DB_001000 DB_001000 Whick'

  UNION ALL

  SELECT 8, 'Good evening DB_001000 DB_001000 Doc'

  UNION ALL

  SELECT 9, 'I know it''s 4am but... DB_001000, DB_001000 , DB_001000 , DB_001000 , DB_001000 Grumpy'

  UNION ALL

  SELECT 10, 'I know it''s the weekend but... DB_001000DB_001000 , DB_001000 , DB_001000 , DB_001000 , DB_001000 Happy'

  UNION ALL

  SELECT 11, 'I need this in 30 seconds. DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 Sleepy'

  UNION ALL

  SELECT 12, 'Does data have feelings? DB_001000 DB_001000 Bashful'

  UNION ALL

  SELECT 13, 'Now now now DB_001000 DB_001000 DB_001000 Sneezy'

  UNION ALL

  SELECT 14, 'Harumph DB_001000 Dopey'

  UNION ALL

  SELECT 15, 'So long DB_001000,DB_001000 ,DB_001000 Huckepack'

  UNION ALL

  SELECT 16, 'Farewell DB_001000 DB_001000 Naseweis'

  UNION ALL

  SELECT 17, 'Auf wiedersehen DB_001000 Packe'

  UNION ALL

  SELECT 18, 'Goodbye DB_001000 DB_001000 DB_001000 Pick'

  UNION ALL

  SELECT 19, 'Swing DB_001000 DB_001000 Puck'

  UNION ALL

  SELECT 20, 'Batter DB_001000 DB_001000 Purzelbaum'

  UNION ALL

  SELECT 21, 'Batter DB_001000 Rumpelbold'

  )

  SELECT b.BugID, SUBSTRING(b.BugComment, t.tally, CHARINDEX(' ', b.BugComment, t.tally + 1) - t.tally) AS db_name

  FROM bug b

  INNER JOIN cteTally10K t ON

  SUBSTRING(b.BugComment, t.tally, 7) LIKE 'DB[_][0-9][0-9][0-9][0-9]'

  ORDER BY b.BugID, t.tally

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• sqldriver

  SSChampion

  Points: 10440

  More actions

  July 14, 2014 at 1:59 pm

  #1729264

  ScottPletcher (7/14/2014)

  ---

  Not sure specifically what you're looking for, maybe this can help:

  ;WITH

  cteTally10 AS (

  SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

  ),

  cteTally100 AS (

  SELECT 1 AS tally

  FROM cteTally10 c1

  CROSS JOIN cteTally10 c2

  ),

  cteTally10K AS (

  SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally

  FROM cteTally100 c1

  CROSS JOIN cteTally100 c2

  ),

  bug (BugID, BugComment) AS (

  SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'

  UNION ALL

  SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'

  UNION ALL

  SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

  UNION ALL

  SELECT 4, 'OH GOD I''M ON FIRE DB_001000 Plick'

  UNION ALL

  SELECT 5, 'Where is SQL? DB_001000, DB_001000, DB_001000 Quee'

  UNION ALL

  SELECT 6, 'Good morning DB_001000 DB_001000 DB_001000 Snick'

  UNION ALL

  SELECT 7, 'Good afternoon DB_001000 DB_001000 DB_001000 Whick'

  UNION ALL

  SELECT 8, 'Good evening DB_001000 DB_001000 Doc'

  UNION ALL

  SELECT 9, 'I know it''s 4am but... DB_001000, DB_001000 , DB_001000 , DB_001000 , DB_001000 Grumpy'

  UNION ALL

  SELECT 10, 'I know it''s the weekend but... DB_001000DB_001000 , DB_001000 , DB_001000 , DB_001000 , DB_001000 Happy'

  UNION ALL

  SELECT 11, 'I need this in 30 seconds. DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 Sleepy'

  UNION ALL

  SELECT 12, 'Does data have feelings? DB_001000 DB_001000 Bashful'

  UNION ALL

  SELECT 13, 'Now now now DB_001000 DB_001000 DB_001000 Sneezy'

  UNION ALL

  SELECT 14, 'Harumph DB_001000 Dopey'

  UNION ALL

  SELECT 15, 'So long DB_001000,DB_001000 ,DB_001000 Huckepack'

  UNION ALL

  SELECT 16, 'Farewell DB_001000 DB_001000 Naseweis'

  UNION ALL

  SELECT 17, 'Auf wiedersehen DB_001000 Packe'

  UNION ALL

  SELECT 18, 'Goodbye DB_001000 DB_001000 DB_001000 Pick'

  UNION ALL

  SELECT 19, 'Swing DB_001000 DB_001000 Puck'

  UNION ALL

  SELECT 20, 'Batter DB_001000 DB_001000 Purzelbaum'

  UNION ALL

  SELECT 21, 'Batter DB_001000 Rumpelbold'

  )

  SELECT b.BugID, SUBSTRING(b.BugComment, t.tally, CHARINDEX(' ', b.BugComment, t.tally + 1) - t.tally) AS db_name

  FROM bug b

  INNER JOIN cteTally10K t ON

  SUBSTRING(b.BugComment, t.tally, 7) LIKE 'DB[_][0-9][0-9][0-9][0-9]'

  ORDER BY b.BugID, t.tally

  Thanks. That would probably work, but there's apparently even more inconsistencies in the data than I allowed for. I keep getting an invalid length error after it runs for a couple minutes.

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  July 14, 2014 at 2:30 pm

  #1729276

  D'OH, sorry, I left off one all-important WHERE condition, an absolute NO-NO for a DBA :-):

  ;WITH

  cteTally10 AS (

  SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

  ),

  cteTally100 AS (

  SELECT 1 AS tally

  FROM cteTally10 c1

  CROSS JOIN cteTally10 c2

  ),

  cteTally10K AS (

  SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally

  FROM cteTally100 c1

  CROSS JOIN cteTally100 c2

  ),

  bug (BugID, BugComment) AS (

  SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'

  UNION ALL

  SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'

  UNION ALL

  SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

  UNION ALL

  SELECT 4, 'OH GOD I''M ON FIRE DB_001000 Plick'

  UNION ALL

  SELECT 5, 'Where is SQL? DB_001000, DB_001000, DB_001000 Quee'

  UNION ALL

  SELECT 6, 'Good morning DB_001000 DB_001000 DB_001000 Snick'

  UNION ALL

  SELECT 7, 'Good afternoon DB_001000 DB_001000 DB_001000 Whick'

  UNION ALL

  SELECT 8, 'Good evening DB_001000 DB_001000 Doc'

  UNION ALL

  SELECT 9, 'I know it''s 4am but... DB_001000, DB_001000 , DB_001000 , DB_001000 , DB_001000 Grumpy'

  UNION ALL

  SELECT 10, 'I know it''s the weekend but... DB_001000DB_001000 , DB_001000 , DB_001000 , DB_001000 , DB_001000 Happy'

  UNION ALL

  SELECT 11, 'I need this in 30 seconds. DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 Sleepy'

  UNION ALL

  SELECT 12, 'Does data have feelings? DB_001000 DB_001000 Bashful'

  UNION ALL

  SELECT 13, 'Now now now DB_001000 DB_001000 DB_001000 Sneezy'

  UNION ALL

  SELECT 14, 'Harumph DB_001000 Dopey'

  UNION ALL

  SELECT 15, 'So long DB_001000,DB_001000 ,DB_001000 Huckepack'

  UNION ALL

  SELECT 16, 'Farewell DB_001000 DB_001000 Naseweis'

  UNION ALL

  SELECT 17, 'Auf wiedersehen DB_001000 Packe'

  UNION ALL

  SELECT 18, 'Goodbye DB_001000 DB_001000 DB_001000 Pick'

  UNION ALL

  SELECT 19, 'Swing DB_001000 DB_001000 Puck'

  UNION ALL

  SELECT 20, 'Batter DB_001000 DB_001000 Purzelbaum'

  UNION ALL

  SELECT 21, 'Batter DB_001000 Rumpelbold'

  )

  SELECT /*DISTINCT*/

  b.BugID, SUBSTRING(b.BugComment, t.tally, CHARINDEX(' ', b.BugComment, t.tally + 1) - t.tally) AS db_name

  FROM bug b

  INNER JOIN cteTally10K t ON

  t.tally <= LEN(b.BugComment) - 7 AND

  SUBSTRING(b.BugComment, t.tally, 7) LIKE 'DB[_][0-9][0-9][0-9][0-9]'

  ORDER BY b.BugID, t.tally --remove ", t.tally" if using DISTINCT

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 14, 2014 at 2:33 pm

  #1729278

  Maybe something that's been more tested could help. 🙂

  WITH bug (BugID, BugComment) AS (

  SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'

  UNION ALL

  SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'

  UNION ALL

  SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

  UNION ALL

  SELECT 4, 'OH GOD I''M ON FIRE DB_001000 Plick'

  UNION ALL

  SELECT 5, 'Where is SQL? DB_001000, DB_001000, DB_001000 Quee'

  UNION ALL

  SELECT 6, 'Good morning DB_001000 DB_001000 DB_001000 Snick'

  UNION ALL

  SELECT 7, 'Good afternoon DB_001000 DB_001000 DB_001000 Whick'

  UNION ALL

  SELECT 8, 'Good evening DB_001000 DB_001000 Doc'

  UNION ALL

  SELECT 9, 'I know it''s 4am but... DB_001000, DB_001000 , DB_001000 , DB_001000 , DB_001000 Grumpy'

  UNION ALL

  SELECT 10, 'I know it''s the weekend but... DB_001000DB_001000 , DB_001000 , DB_001000 , DB_001000 , DB_001000 Happy'

  UNION ALL

  SELECT 11, 'I need this in 30 seconds. DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 DB_001000 Sleepy'

  UNION ALL

  SELECT 12, 'Does data have feelings? DB_001000 DB_001000 Bashful'

  UNION ALL

  SELECT 13, 'Now now now DB_001000 DB_001000 DB_001000 Sneezy'

  UNION ALL

  SELECT 14, 'Harumph DB_001000 Dopey'

  UNION ALL

  SELECT 15, 'So long DB_001000,DB_001000 ,DB_001000 Huckepack'

  UNION ALL

  SELECT 16, 'Farewell DB_001000 DB_001000 Naseweis'

  UNION ALL

  SELECT 17, 'Auf wiedersehen DB_001000 Packe'

  UNION ALL

  SELECT 18, 'Goodbye DB_001000 DB_001000 DB_001000 Pick'

  UNION ALL

  SELECT 19, 'Swing DB_001000 DB_001000 Puck'

  UNION ALL

  SELECT 20, 'Batter DB_001000 DB_001000 Purzelbaum'

  UNION ALL

  SELECT 21, 'Batter DB_001000 Rumpelbold'

  )

  SELECT *

  INTO #bug

  FROM bug

  --Line per DB

  SELECT BugID,

  BugComment,

  s.Item

  FROM #bug

  CROSS APPLY (SELECT REPLACE( BugComment, ',', ' ') Comment) Clean

  CROSS APPLY DelimitedSplit8K(Clean.Comment, ' ') s

  WHERE Item LIKE 'DB[_][0-9][0-9][0-9][0-9]%'

  ORDER BY BugID ;

  --Line per bug

  SELECT BugID,

  BugComment,

  STUFF((SELECT ', ' + s.Item

  FROM (SELECT REPLACE( BugComment, ',', ' ') Comment) Clean

  CROSS APPLY DelimitedSplit8K(Clean.Comment, ' ') s

  WHERE Item LIKE 'DB[_][0-9][0-9][0-9][0-9]%'

  FOR XML PATH('')), 1, 2, '')

  FROM #bug

  ORDER BY BugID

  GO

  DROP TABLE #bug

  EDIT: Forgot to include the reference for the DelimitedSplit8K function[/url].

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sqldriver

  SSChampion

  Points: 10440

  More actions

  July 14, 2014 at 4:35 pm

  #1729302

  Good call on that. Doing this sort of forced me to look at the underlying table structure of the half-aliased mess of a view I inherited. I have the below code working and giving me most of what I need. Tomorrow I'll get the rest of it in place. Thanks again to both of you for the input.

  SELECT DISTINCT b.bp_bug, SUBSTRING(c.Item, 0, 10) AS Item

  FROM bug_posts b

  CROSS apply MASTER.dbo.DelimitedSplit8K(b.bp_comment_search, ' ') c

  WHERE c.item LIKE 'DB_[0-9][0-9][0-9][0-9]%'

  AND b.bp_date >= DATEADD(MONTH, -3, GETDATE())

  AND EXISTS (

  SELECT 1

  FROM bugs bg

  WHERE b.bp_bug = bg.bg_id

  AND bg.bg_project_custom_dropdown_value1 = 'Backup/Restore'

  AND bg.bg_status = 5

  )

  ORDER BY b.bp_bug

• sqldriver

  SSChampion

  Points: 10440

  More actions

  July 15, 2014 at 8:13 am

  #1729475

  Heh.

  Msg 6841, Level 16, State 1, Line 166

  FOR XML could not serialize the data for node 'NoName' because it contains a character

  (0x0012) which is not allowed in XML. To retrieve this data using FOR XML, convert it

  to binary, varbinary or image data type and use the BINARY BASE64 directive.