Skip to main content
Delphix

Profile Data in Database Column for Oracle (KBA4448)

 

KBA

KBA# 4448

 

At a Glance

Versions: Applicable Delphix Masking versions: 4.x, 5.x, 6.x
Description: This page shows queries to use on Oracle to profile data. This is especially handy to discover non-conforming data. 
Queries applicable to: Oracle
Non-Conforming Classification: This is useful to identify Non-Conforming characters on the Masking Engine.
The Classification used on the engine is: 
  1. Letters (L)
  2. Numbers (N)
  3. Marks (M)
  4. Separators (Z)
  5. Punctuation (P)
  6. Symbols (S)
  7. Other (O)

For details about the Unicode characters please look here: 
https://www.compart.com/en/unicode/category/

Queries listed:  The following queries are listed below: 
  • How many rows in the table?
  • How many rows with a specific length?
  • How many rows with a specific profile?
  • How many rows with special characters in the cell?
  • What rows have Special Characters (how to find them)?

Profiling of Data in Masked Column

These queries (and functions) are used to profile data in columns to be masked to understand what algorithm is best suited and how to configure the algorithm. Unicode character profile has been used to match the patterns reported in the masking engine's Monitor Page for nonconforming data. 

The following codes have been used: 

  • N - Numbers (0-9)
  • L - Letters (A-Z)
  • Z - Space
  • All the other characters are shown.

Queries for Oracle 

How many rows in the table?

This is important to know to understand the statistical distribution of the profile result below. 

-- Replace [table]
--  
SELECT COUNT(*) cnt_rows FROM [table];
Example
 CNT_ROWS 
----------
  4534824 

How many rows with a specific length?

This query shows the number of rows with a specific length (number of characters). The query has been limited to only count the first 1,000 rows. 

-- Replace [table]
-- Replace [mask_col]
-- 
SELECT LENGTH([mask_col]) as str_len, COUNT(*) as cnt 
FROM [table] 
WHERE ROWNUM < 1001
GROUP BY LENGTH([mask_col]) 
ORDER BY cnt DESC;
Example

Some algorithms are specifically created to fit a specific length. This example shows 'Home Phone Number' and there are clearly some additional data other than phone numbers in here. 

 STR_LEN       CNT 
--------- ---------
 10        727,204 
 11        399,465 
 12        104,710 
 9          81,835 
...
 24              2 
 25              1 
 26              1 

How many rows with a specific profile?

This query shows the number of rows with a specific length (number of characters). The query has been limited to only count the 1,000 first rows. 

-- Create Function Below
-- Replace [table]
-- Replace [mask_col]
-- 
SELECT profileASCII([mask_col]) as profile, Count(*) as cnt 
FROM [table]
WHERE ROWNUM < 1001
GROUP BY profileASCII([mask_col]) 
ORDER BY cnt DESC;
Function

To use the query above, please create this function first. It will profile data based on: 

  • N - Numbers (0-9)
  • L - Letters (A-Z)
  • Z - Space
  • All the other characters are shown.
-- ORACLE
CREATE OR REPLACE FUNCTION profileASCII (inpStr in VARCHAR2)
RETURN VARCHAR2 IS
   tmpChr VARCHAR2(10);
   tmpStr VARCHAR2(10);
   resStr VARCHAR2(1000);
   increment INT := 1;
BEGIN
   WHILE increment <= LENGTH(inpStr)
   LOOP
     tmpChr := SUBSTR(inpStr, increment, 1);
     IF REGEXP_LIKE(tmpChr,'[0-9]') THEN
        tmpStr := 'N';
     ELSIF REGEXP_LIKE(tmpChr, '[a-z,A-Z]') THEN
        tmpStr := 'L';
     ELSIF REGEXP_LIKE(tmpChr, '[ ]') THEN
        tmpStr := 'Z';
     ELSE
        tmpStr := tmpChr;
     END IF;
     resStr := resStr || tmpStr;
     increment := increment + 1;

   END LOOP;
   RETURN resStr;
END;
/
Example

The example below shows an example from profiling 'Salutation' (for example 'Mr'). The example shows the length and that the data has some records with full stop and some without. 

 profile       cnt 
--------- ---------
 LL        376,849 
 LL.       206,021 
 LLL.       34,005 
 LLLL       31,291 
 LLL           127 
 LLLL.           3 

How many rows with special characters in the cell?

This query has specifically been created to extract out special characters in data that needs to be ignored in masking algorithms. The query shows the number of rows with special characters. The query has been limited to only count the 1,000 first rows. 

-- Create Function Below
-- Replace [table]
-- Replace [mask_col]
-- 
SELECT profileSpecialASCII([mask_col]) as special, Count(*) as cnt 
FROM [table]
WHERE ROWNUM < 1001
GROUP BY profileSpecialASCII([mask_col]) 
ORDER BY cnt DESC;
Function 

To use the query above, please create this function first. 

  • All the other characters are only shown.
-- ORACLE
CREATE OR REPLACE FUNCTION profileSpecialASCII (inpStr in VARCHAR2)
RETURN VARCHAR2 IS
   tmpChr VARCHAR2(10);
   tmpStr VARCHAR2(10);
   resStr VARCHAR2(1000);
   increment INT := 1;
BEGIN
   WHILE increment <= LENGTH(inpStr)
   LOOP
     tmpChr := SUBSTR(inpStr, increment, 1);
     IF REGEXP_LIKE(tmpChr,'[0-9,a-z,A-z, ]') THEN
        tmpStr := '';
     ELSE
        tmpStr := tmpChr;
     END IF;
     resStr := resStr || tmpStr;
     increment := increment + 1;

   END LOOP;
   RETURN resStr;
END;
/
Example

Finding special characters in the data could be very useful and tells a lot about the data - in some cases, it needs to be taken care of. The sample below is taken from a 'Fullname' column. 

 special       cnt 
--------- ---------
 _         205,585 
 _&         98,783 
 _-         72,624 
 _++        24,233 
 _'         17,766 
 _()        13,579 
 _/         12,158 
 _&&         8,431 
 _.          8,053 
 _**         7,714 
 _+          7,425 
 _*          6,294 
 _//         4,992 
 _#          4,213 
 _****       4,162 
 _--         3,677 
 _,          1,365 
 _()*        1,245 
 _(/)        1,204 
 _---        1,146 

What rows have Special Characters (how to find them)?

This query can be used to show the first records in the database with Special Characters. The number of rows selected might need to be changed.

-- Replace [table]
-- Replace [mask_col]
-- Modify 1001 if more sample data is needed.

SELECT ROWID, [mask_col] FROM [table]
WHERE ROWNUM < 1001
AND regexp_like([mask_col],'[^a-zA-Z0-9]');