Q:

Select only Value Length & Format - MySQL

Is there a way to select only fields that meet the following criteria from a specific column in mysql:
MAX Length = 9
First 3 characters must be HSA
Last 6 characters must only be INT
The 6 digits must start with 0... This may change later to start with 1 / 2 etc.
The below table shows the basic schema and possible fields values. 
row | visit_date | field_value 
----+------------+--------------- 
1   |2016-12-07  |HSA023456   
2   |2016-12-08  |HSA059870 
3   |2016-12-06  |225981450    
4   |2016-12-05  |HSAB02354    
5   |2016-12-13  |HSA0234567   
6   |2016-12-14  |HSA    
7   |2016-12-14  |HSA0987    
8   |2016-12-16  |1234HSA00

Rows 1 - 3 are valid rows and would have to be returned.
Rows 4 - 8 are invalid rows and will have to be igonred.
I have tried the below, although this does not provide the correct result as it only provide a partial solution:
,       CASE
            WHEN CHAR_LENGTH(NFB.field_value) > 8 AND NFB.field_value LIKE 'HSA%' THEN UPPER(NFB.field_value)
            WHEN CHAR_LENGTH(NFB.field_value) < 9 AND NFB.field_value LIKE 'HSA%' THEN CONCAT(UPPER(NFB.field_value),'VALIDATE')   

ELSE ""
            END field_value
Thanks

A:

This query is a good candidate for using MySQL's regex operator REGEXP.  Note that all your requirements can be met by matching with the following regex:
^HSA0[0-9][0-9][0-9][0-9][0-9]$

This matches 9 characters exactly, which begin with HSA0 followed by any other five digits.
SELECT *
FROM yourTable
WHERE field_value REGEXP '^HSA0[0-9][0-9][0-9][0-9][0-9]$'

Update:
If you want to restrict to only uppercase, then you could add a LIKE condition checking for this:
SELECT *
FROM yourTable
WHERE field_value REGEXP '^HSA0[0-9][0-9][0-9][0-9][0-9]$' AND
      field_value LIKE BINARY 'HSA%'

REGEXP is case insensitive, so a workaround is required.

