Skip to content

Instantly share code, notes, and snippets.

@sheldonhull
Created July 14, 2016 15:26
Show Gist options
  • Save sheldonhull/b067b6d87cda11d70c608298cff8c0d4 to your computer and use it in GitHub Desktop.
Save sheldonhull/b067b6d87cda11d70c608298cff8c0d4 to your computer and use it in GitHub Desktop.
Parsing match from text using SQL# (SqlSharp) CLR function
declare @StuckGum as table (
message varchar(1000) not null
);
insert into @StuckGum
( message )
-- Data generated by http://bit.ly/29GsjgG thanks for such a great resource for cool randomized data
values
( 'A media personality named Hou Pu-Sho needs a 4:37: team to abduct and extract Arsem Tchinova, the head of newLink Unlimited. Further, security is already on alert because of a previous botched run.' )
, ( 'A charming corporate agent named Dara Yenkotov needs a 2:41: team to abduct and extract Take Kuri, the head of Paronki-Puchkora Multinational. Further, security is already on alert because of a previous botched run.' )
, ( 'A guarded media personality named Tobias McRaven needs a 8:37: team to abduct and extract Nouchi Sumi, the head of priSoft Consolidated. However, he cannot provide any usual form of compensation.' )
, ( 'A data broker named Ientik Sova needs a team 8:38: to assassinate a rival named Nake Tomi. However, the team finds itself double-crossed.' )
, ( 'A hawkish data broker named Denisse Bechtel needs a 9:33: team to break into the network of OI Multinational. However, the team finds itself double-crossed.' )
, ( 'A guarded data broker named Kada Ekomi needs a 2:183: team to abduct and extract Lincoln Watrous, a media officer of Dee-Kui Amalgamated. In addition, the team is randomly targeted by a mischievous hacker.' )
, ( 'A government official named Ghislaine Tronstad needs a team 5:163: to break into the network of IBH Amalgamated and assassinate an advanced artificial intelligence. However, the job is a trap.' )
, ( 'A foreign government agent named Merrill Arleth needs a 8:92: team to deliver a black box to Zhieu Corporation. Further, the team has only thirty minutes to complete the job.' )
, ( 'A cryptic hacker named Necole Tower needs a team 6:46: to break into the network of Veillon-Naki Multinational and steal military data. Further, the security surrounding the target is unusually high.' )
, ( 'A corporate agent named Lena Mova needs a team 2:181: to assassinate a rival named McKinley Vangelos. However, the job is a trap.' );
-- Text I'm trying to match is the #:###:
-- Like statement doesn't work due to number being 1-2 at beginning followed by colon with 1-3 numbers followed by colon.
-- This requires the ability to match a varying length of numbers, which like can't do.
select
ParsedNumber = SQL#.RegEx_CaptureGroup(sg.message, '.*(\d{1,2}:\d{1,3}):.*', 1, null, 1, -1, '')
,sg.message
from
@StuckGum as sg;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment