RegEx to blank everything not 11 characters long

I think this one is a simple question but I am not quite good enough with RegEx to figure it out. I am looking to blank out any strings of numbers which are not exactly 11 characters long (as they are telephone numbers). I intend to use RegEx replace to replace with “”, but I cannot quite get it to pick up what I need in the tester I am using. For example, I would like:

01111999999 → Keep
01111 → change to blank
0111199988 → change to blank

I have managed to take off all which are over 11, but I cannot find anything which accounts for “less than”. Is that possible? Thanks!

Hi @JWebb

You can quickly achieve this by matching anything that is not equal to 11 subsequent numbers. With Regex, you can control this length by using {}. In your case, the Regex ([0-9]{11}) matches 11 numbers consisting of anything between 0 and 9.

In KNIME terms, you can apply this by checking if the length of the numbers equals 11 keep it, else make it “”.

See below example:

To better understand how this works, I added a regexMatcher with mentioned Regex. This highlights in which rows matching values were found.

Afterwards, you can apply the logic that if the match is true keep the value, otherwise make it blank.

Note1: Since the evaluation code already includes the matcher, you don’t need this separately (only for illustration purposes)
Note2: this assume that you have phone numbers only represented by numbers, excluding spaces etc. Any other formats require tweaks to the RegEx.

RegEx to blank everything not 11 characters long.knwf (12.4 KB)

Hope this helps!


Perfect! Thanks. That did absolutely what I needed to (though I had to build in extra to cope with missing values). That’s really useful and I wasn’t aware column expressions could be so powerful. All looks good with my dataset now :slight_smile:


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.