How to apply a regex on a column fetched from DB

Question

I am using IBM DB2.

So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column:

([1-57-8][0-9]{2}
|0([1-9][0-9]
|[0-9][1-9])
|6([0-57-9][0-9]
|[0-9][0-57-9]))
([1-9][0-9]|[0-9][1-9])([1-9]\d{3}|\d[1-9]\d{2}|\d{2}[1-9]\d|\d{3}[1-9])

Someone suggested to me that I have to write a function/procedure in the query to validate that the SSN picked from the db follows this regex else it should not be stored in the result set of the query.

I don't know how to write a function or a procedure in SQL, can you please help me out here?

Answer 1

You can use Regex with UDFs (user-defined functions). What you need is to create a Function that will call a Regex library. After that, your SQL will call this Function to execute the Regex.

First, read the following article as an example.

It says that you can create this Function as:

CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M)) 
 RETURNS INTEGER 
 SPECIFIC regexSimple 
 EXTERNAL NAME 'regexUdf!regexpSimple' 
 LANGUAGE C 
 PARAMETER STYLE DB2SQL 
 DETERMINISTIC 
 NOT FENCED 
 RETURNS NULL ON NULL INPUT 
 NO SQL 
 NO EXTERNAL ACTION 
 ALLOW PARALLEL;

The article also shows the C library code and indicates how you can compile it.

After that, you may use your function like that:

SELECT c1, str 
FROM strTable 
WHERE regex1('#\s*\w+', str) = 1;

With that in mind, I suggest that you Google a little more to find a more complete Regex library to resolve your problem.

Edited: as suggested by Chris Aldrich, you may consider on running this function as fenced for safety. Read more here.

• Chris Aldrich: I would vote that the function is run FENCED since it calls out to a library. If the library ever crashes it has the possibility of endangering the DB2 engine if it is running NOT FENCED.