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.