Profiling Data in Database Column for MySQL (KBA5238)

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];

 cnt_rows
  4534824 

How many rows with a specific length (characters and bytes)? 

This query shows the number of rows with a specific length. There are two queries - the first for characters and the second for bytes. The query has been limited to only count the first 1,000 rows. 

For memory-related issues use bytes. For masking results, use characters. 

Note:

The LENGTH function might need to be replaced for specific datatypes. 

-- Replace [table]
-- Replace [mask_col]
-- Only displaying top 1000 rows. Change if more is needed. 
--
SELECT foo.str_len, COUNT(*) cnt 
FROM (SELECT CHAR_LENGTH([mask_col]) str_len FROM [table] LIMIT 1000) AS foo
GROUP BY 1
ORDER BY 2 DESC;

SELECT foo.bytes, COUNT(*) cnt
FROM (SELECT LENGTH([mask_col]) bytes FROM [table] LIMIT 1000) AS foo
GROUP BY 1
ORDER BY 2 DESC;

 str_len       cnt 
 10            204 
 11             65 
 12             10 
...
 24              2 
 25              1 
 26              1 

How many duplicates (is the data unique)?

Use this query when the uniqueness of the values in a column is important. This query should be executed on the column before and after masking. Especially investigate columns that are referential keys (i.e., PK and FK) or have unique constraints. Note that these might be composite and multiple columns may need to be investigated. Also, values might need to be formatted using a cast. 

This query shows if there are duplicated values in a column. The expected result is one row with 'dups' = 1.

Note:

No values from the columns are shown. This query only shows statistics (number of duplicates). 

-- Replace [table]
-- Replace [column]
--
-- dups should be 1 for no duplicates.
--
SELECT count(*) as cnts, s1.dups
FROM (
   SELECT [column], count(*) dups
   FROM [table]
   GROUP BY [column]) s1
GROUP BY s1.dups;

Concatenate columns (using | as a separator as it is not frequently used):

CONCAT(column1, "|", column2)

      cnts    dups
   4534822       1
         1       2

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]
GROUP BY profileASCII([mask_col])
ORDER BY cnt DESC
LIMIT 50;

-- MySQL
CREATE FUNCTION profileASCII(inStr VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
  BEGIN
    DECLARE tmpChr varchar(10);
    DECLARE tmpStr VARCHAR(1000);
    DECLARE incr INT;
    SET incr = 1;
    SET tmpStr = '';

    WHILE incr <= LENGTH( inStr ) DO
       SET tmpChr = LOWER(SUBSTRING( inStr, incr, 1));
       IF FIND_IN_SET( tmpChr, '0,1,2,3,4,5,6,7,8,9') > 0 THEN 
           tmpChr = 'N'
       ELSEIF FIND_IN_SET( tmpChr, 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z') > THEN
           tmpChr = 'L'
       ELSEIF tmpChr = ' ' THEN
           tmpChr = 'Z'
       tmpStr = CONCAT( tmpStr, tmpChr)
       SET incr = incr + 1;
    END WHILE;
    RETURN inStr;
  END;

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

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]
-- Limited to 50 rows.

SELECT [mask_col] FROM [table]
WHERE [mask_col] REGEXP '[^a-zA-Z0-9]'
LIMIT 50;