My Regular Expressions Love-Hate Relationship

Category : Technical Goodies

I still remember we first met when I was in University where we got off on a rocky start without any positive feelings towards each other due to the lack of understanding and the breakdown in communication. I was literally pulling my hairs out with throbbing headaches while re maintained its stance and stared coldly back at me. The relationship was time-bound and I’m glad it didn’t last as I toss it behind me shortly after.

Lo and behold, re paid me a visit after I graduated into the workplace and we were each given a 2nd shot at each other. I’ve since grown to love re albeit with occasional teeth gritting moments just like today when I engaged re to participate in yet another data issue which was eventually resolved elegantly.

Curious about re? Let me formally introduce or, in some cases, reacquaint you with re whose full name is Regular Expressions. Being in the data management business, I’ve got to admit that Regular Expression is extremely helpful in more ways than one, from simple perl subroutines to complex data cleansing accomplished with a single line of code versus countless sql replace statements. Let me elaborate:

Goal
Concatenate multiple user ids in a single cell with a semicolon delimiter
E.g. 123;456;789

Challenge
Data source contains multiple user ids with various descriptive tags assigned to each id
E.g.  123 (IT Prof ID);456 (Gamer ID),(Generic ID) 789;

Without Regular Expression

DECLARE@String char(50)
SET @String = '123 (IT Prof ID);456 (Gamer ID),(Generic ID) 789;'

SELECT @String OriginalString, @String String INTO #Temp

UPDATE #Temp SET String = REPLACE(String,'(IT Prof ID)','')
UPDATE #Temp SET String = REPLACE(String,'(Gamer ID)','')
UPDATE #Temp SET String = REPLACE(String,'(Generic ID)','')

UPDATE #Temp SET String = REPLACE(String,',',';')
UPDATE #Temp SET String = REPLACE(String,'; ',';')
UPDATE #Temp SET String = REPLACE(String,' ;',';')
UPDATE #Temp SET String = LTRIM(RTRIM(String))
UPDATE #Temp SET String = CASE WHEN RIGHT(String,1) = ';' THEN LEFT(String,LEN(String)-1) ELSE String END

SELECT OriginalString, String /*123;456;789*/
FROM #Temp

DROP TABLE #Temp

With Regular Expression

DECLARE@String char(50)
SET @String = '123 (IT Prof ID);456 (Gamer ID),(Generic ID) 789;'

SELECT @String OriginalString, @String String INTO #Temp

SELECT OriginalString, RegularExpressionString = RegexReplace(';$','',RegexReplace(' *;+ *',';',RegexReplace('\(.+?\)|,',';',OriginalString,1,1),1,1),1,1) FROM #Temp

DROP TABLE #Temp

Notes
Using the usual REPLACE syntax may not capture all potential descriptive tags i.e. for each newly discovered tag, a new REPLACE statement have to be added to the code. Whereas the Regular Expression syntax will capture everything enclosed in ().

Regular Expression Syntax
1. ‘\(.+?\)|,’,’;’ Replace everything enclosed within () with ;
2. ‘ *;+ *’,’;’    Replace any number of ; (e.g. ;;, ;;;, ;;;;, etc) with a single ; and remove any prefix & trailing blankspace
3. ‘;$’,”           Remove ; from the end of the string if exists

Additional notes
Another useful Regular Expression is one which removes html tags:
RegexReplace(‘<(?:[^>””]*|([””]).*?\1)*>|&nbsp;|^[ \t\n\r]+|[ \t\n\r]+$’,, OriginalString,1,1)

Reference

Here’s the code behind the RegexReplace function

CREATE FUNCTION [dbo].[RegExReplace] 
(@Input [nvarchar](512), @Pattern [nvarchar](127), @Replacement [nvarchar](512))
RETURNS [nvarchar](512) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [RegularExpressionFunctions].[RegularExpressions].[RegExReplace]
GO

And if you’ll like to start using Regular Expressions i.e. the RegexReplace function or more applications of this wonderful concept – http://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/

Hope this helps any data analyst or business intelligence analyst out there who’s facing challenges with their data.

On a seperate note, if you prefer to flip though the pages or have a handy book at your side:

 

Everybody wants a piece of me

Category : Workplace

The thing with working in a MNC is that the world continues spinning while Singapore is having a public holiday. The feeling that I got when I first opened my email is, yes, everyone wants a piece of me. My team has been shrinking over the past couple of months with no new hires and guess what? The workload didn’t adjust proportionally. What’s new right? I’m sure you know what I mean.

Besides being technically involved in four to five concurrent projects, they seemed to have quickened their pace while I was taking a breather over Deepavali, the festival of lights. Ironically I don’t see many friendly lights awaiting me in the treacherous forest ahead. Bleak? I know.

So I ask myself this question, is it even possible to deliver or catch up on all the tasks and user requests on my plate without burning any evenings and weekends? If I choose to ‘sell my soul’ to my company, what’s next?

Yes, indeed I’m at a crossroad with many thoughts firing away as I go about my everyday life, building my storyboard, ideally one with abundance, happiness and most importantly – choice. Achievable? What about your life story? What have you pictured it to be and are you living it yet? If not, what are you doing (if you are doing something) towards aligning your life with your dream?

Thoughts will just be thoughts. As I wave goodbye to them followed by a few deep breathes, I returned to reprioritizing my work based on what’s in my calendar for the day. Things will take a turn for the better. I believe it will.

Hi, I’m Soh Cheng :)

Category : General

Wow, my maiden post on my very own blog. What should I write about? My journey from a blur & naive fresh graduate to a weathered & skeptical business intelligence analyst? My life experiences, random thoughts or crazy ideas that just pop into my mind? On what note should I start off on?

Maybe I’ll just keep things simple and blog about myself first. I’ve been working for 7 years now and am currently a Business Intelligence Analyst working in a MNC using mainly Microsoft technologies like reporting & integration services.

Come to think of it, this wasn’t the path I had envisioned myself taking having graduated from a Computer Engineering Course with no relevant experience in this field. Yet by a twist of path I was assigned to the data management team in my sponsoring company and through the mentorship of my managers and seniors, I’ve grown to love data analytics.

I indulge in the fun of analyzing data, identify trends and asking critical questions to the business beyond what was initially placed on the plate. In other words, value-add. However I also have to acknowledge that despite I love what I’m doing, it may not be possible to continue doing so due to pressures from being a ‘cost centre’ and branded as more expensive as compared to offshore/outsourcing alternatives.

So it seems like I have come to a crossroads where I have to decide if I should turn my back on my education and passion versus accepting a pay freeze or worse, a pay cut. Unfortunately the future seems pretty grim to me. I hope I’m wrong…