Pattern recognition in a text field
Posted on 2008-11-07
I am doing some data profiling and want to create a procedure which will provide back the pattern of the data in the text field. In my first case, this is phone number. So, for each phone number in a table I have data in, I want to parse through the data element and return the pattern. For example, if the field was a phone number, I would expect patterns consisting of Numbers (N), Characters (A) and special or delimiting characters. If I were to pass in data that looked like this: (123) 234-3456, the return pattern would need to be (NNN) NNN-NNNN. If the phone looked like this 123.L16.1234, then the return pattern would need to look like this NNN.NNN.NNNN. I am thinking that the solution will including using regular expressions from Oracle, but I just can't wrap my head around the best way to do this. Any help would be appreciated. Thanks!