Check for pattern in SSIS

Question

Post reply

Check for pattern in SSIS

PSB

SSChampion

Points: 13195
More actions
September 14, 2010 at 10:33 am

#227775

How to validate a column in this format ---> xx-xxxxxxx

Viewing 9 posts - 1 through 8 (of 8 total)

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

PSB (9/14/2010)
How to validate a column in this format ---> xx-xxxxxxx

A script task and Regular expression :rolleyes::hehe::cool:

Raunak J

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Or the clever use of LEN and FINDSTRING in a derived column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

da-zero (9/16/2010)
Or the clever use of LEN and FINDSTRING in a derived column.

😉

I missed here

Raunak J

Todd McDermid Default port Points: 1416 More actions · Answer 4

Use a Conditional Split component to split off the "invalid" rows. Use a condition that checks that the LEN([Column]) == 10, and that SUBSTRING([Column], 3, 1) == "-".

You weren't clear what values were allowable in the "x" positions - so just testing for those two things will allow any values.

Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components

PSB SSChampion Points: 13195 More actions · Answer 5

I was looking for something like this 713-777-9906

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 6

PSB (9/23/2010)
I was looking for something like this 713-777-9906

Now definitely i'll be using a REGEX

Raunak J

PSB SSChampion Points: 13195 More actions · Answer 7

PSB

SSChampion

Points: 13195

September 24, 2010 at 1:43 pm

#1227210

Can you give an example .

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 8

Regex newPattern=new Regex("[0-9]+[-][0-9]+[-][0-9]+")

Explanation: not beginning with alphabets and having atleast one occurance or more of numeric followed by dash(hyphen) which is followed by one or more occurance of numerals again a dash(hypen) and numerals

You may customize the length of the expression to suit your need. :-):-)

Raunak J