SQL StoredProcedure for data validation using REGEX in SSIS

Question

Post reply

SQL StoredProcedure for data validation using REGEX in SSIS

komal145

SSCrazy Eights

Points: 9915
More actions
September 16, 2021 at 3:23 pm

#3930199
hi,
I have a requirement where we get data from Oracle source and destination is also Oracle db. Only validation is done in SSIS. So, i have to validate the data using regular expression in SQL Storedprocedure (in ssis) and send bad data to a error log table with logging the record ( with table name , column name , error code) .
I am new to regular expression , i am kind of confused how to start and how to validate with regular expressions.
Can you please provide simple , sample example to understand.
For example : Age validation : Age cannot be zeros , age cannot be 200 years old or in future.
How do we get simple Storedprocedure for the same?

Thanks again for help in advance.
- This topic was modified 3 years, 2 months ago by komal145.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

This is a Microsoft SQL Server forum, and a SQL Server 2019 - Development channel.
TSQL does not support regex natively, & therefore you probably won't do regex in a TSQL stored procedure unless you implement CLR functions. PLSQL does have some regex functions. But you really do not need regex for the simple criteria you cited.

In this case the where clause filter would seem to simply be:

Age > 0 AND Age < 200

You stated "Age cannot be zeros". I hope that does not mean age is a text/varchar datatype that could include a string of multiple zeroes.

In future does not make sense to me in the context of age. What do you mean when you say age cannot be "in future"?

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 2

I'll also state that RegEx is serious overkill for the type of validations you've described. You certainly don't need CLR, either. The example the ratback provided above will work quite nicely and usually faster than a call to RegEx via a CLR (been there, done that). It's just not necessary for thing like you describe.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

komal145 SSCrazy Eights Points: 9915 More actions · Answer 3

Thanks for the Reply. I was able to validate data using Regular expressions , since we have oracle source data Using Script component as transformation in SSIS. It is way easier to validate the data , record by record , to load data into Target. Replying this thread , as it might help someone.