Created
July 14, 2016 15:26
-
-
Save sheldonhull/b067b6d87cda11d70c608298cff8c0d4 to your computer and use it in GitHub Desktop.
Parsing match from text using SQL# (SqlSharp) CLR function
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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