CREATE FUNCTION `fn_JSON_Extract`(
  details TEXT,
  required_field VARCHAR (256)
) RETURNS text CHARSET latin1
BEGIN
  RETURN TRIM(
    BOTH '"' FROM SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        SUBSTRING_INDEX(
          details,
          CONCAT(
            '"',
            SUBSTRING_INDEX(required_field,'$.', - 1),
            '"'
          ),
          - 1
        ),
        '",',
        1
      ),
      ':',
      - 1
    )
  ) ;
END

{"strength":"38","strength_uom":"mg","dose":"2","dose_uom":"day"}

CAST(fn_JSON_Extract(clinical_comments, 'strength_uom') AS char(2))

mysql> USE MYSQL
Database changed
mysql> DROP FUNCTION IF EXISTS fn_JSON_Extract;
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION `fn_JSON_Extract`(
    ->   details TEXT,
    ->   required_field VARCHAR (256)
    -> ) RETURNS text CHARSET latin1
    -> BEGIN
    ->   RETURN TRIM(
    ->     BOTH '"' FROM SUBSTRING_INDEX(
    ->       SUBSTRING_INDEX(
    ->         SUBSTRING_INDEX(
    ->           details,
    ->           CONCAT(
    ->             '"',
    ->             SUBSTRING_INDEX(required_field,'$.', - 1),
    ->             '"'
    ->           ),
    ->           - 1
    ->         ),
    ->         '",',
    ->         1
    ->       ),
    ->       ':',
    ->       - 1
    ->     )
    ->   ) ;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> DROP TABLE test_tab;
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE TABLE test_tab (test_col CHAR(2));
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO test_tab (test_col)
    ->   SELECT CAST(fn_JSON_Extract('{"strength":"38","strength_uom":"mg","dose":"2","dose_uom":"day"}', 'strength_uom') AS char(2));
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>

CREATE TABLE Patient_Medication as select hr.patient_id,hr.code_id as medication_code,
	(select code_name from PATIENT_DATA.Code where code_id = hr.code_id) as medication_name,
	CAST(fn_JSON_Extract(clinical_comments, ''strength'') AS SIGNED) as strength,
	CAST(fn_JSON_Extract(clinical_comments, ''strength_uom'') AS char(2)) as strength_uom,	
	concat(CAST(fn_JSON_Extract(clinical_comments, ''dose'') AS SIGNED),'' '',
	substr(CAST(fn_JSON_Extract(clinical_comments, ''dose_uom'') AS CHAR(5)),1,3)) as frequency,
	created_date as prescription_date
 from Health_Record