By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT
TRIM(
SUBSTR(
SUBSTR(MYTABLE.A, LOCATE(',', MYTABLE.A) + 1)
, 1
, POSITION(' ' , SUBSTR(MYTABLE.A, LOCATE(',', MYTABLE.A) + 1))
)
) AS STATE
, TRIM(
SUBSTR(
SUBSTR(MYTABLE.A, LOCATE(',', MYTABLE.A) + 1)
, POSITION(' ' , SUBSTR(MYTABLE.A, LOCATE(',', MYTABLE.A) + 1))
)
) AS ZIP
FROM (
VALUES
('GRANADA HILLS ,CA 91344')
, ('SIMI VALLEY ,CA 93065')
, ('GLENDALE ,CA 91203-2089')
, ('SIMI VALLEY ,CA 93065')
, ('SIMI VALLEY ,CA 93065')
, ('GLENDALE ,CA 91203-2089')
) MYTABLE (A)
| STATE | ZIP |
|---|---|
| CA | 91344 |
| CA | 93065 |
| CA | 91203-2089 |
| CA | 93065 |
| CA | 93065 |
| CA | 91203-2089 |