Подстрока из значения в оракуле sql

Похоже на нить. Если это так, то вот один (собственно, два) вариант:

SQL> with test (col) as
  2    (select 'Thu Jan 01 01:00:00 UZT 1970' from dual)
  3  select substr(col, 1, instr(col, ' ', 1, 2)) || substr(col, -4) result,
  4         regexp_substr(col, '(\w+ ){2}') || regexp_substr(col, '\d+$') result2
  5  from test;

RESULT       RESULT2
------------ ------------
Thu Jan 1970 Thu Jan 1970

SQL>

[РЕДАКТИРОВАТЬ]

Как преобразовать его в другой формат? Применив TO_DATE (чтобы преобразовать строку «Четверг, январь 1970», в допустимый формат ДАТЫ), а затем TO_CHAR с соответствующей маской формата, чтобы получить то, что вы хотите.

SQL> with test (col) as
  2    (select 'Thu Jan 01 01:00:00 UZT 1970' from dual),
  3  inter as
  4    (select substr(col, 1, instr(col, ' ', 1, 2)) || substr(col, -4) result,
  5            regexp_substr(col, '(\w+ ){2}') || regexp_substr(col, '\d+$') result2
  6     from test
  7    )
  8  select to_char(to_date(result, 'dy mon yyyy', 'nls_date_language = english'), 'dd-mm-yyyy') new_result
  9  from inter;

NEW_RESULT
----------
01-01-1970

SQL>

Используйте TO_DATE, затем TO_CHAR:

SELECT TO_CHAR(
         TO_DATE(
           'Thu Jan 01 01:00:00 UZT 1970',
           'Dy Mon DD HH24:MI:SS "UZT" YYYY'
         ),
         'Dy Mon YYYY'
       ) AS substring
FROM   DUAL

| SUBSTRING    |
| :----------- |
| Thu Jan 1970 |

дб <> рабочий пример здесь

Если вы хотите, чтобы это был формат DD-MM-YYYY, измените модель формата, используемую в TO_CHAR, на 'DD-MM-YYYY', но если вы собираетесь хранить ее в базе данных, было бы лучше просто сохранить ее как значение даты, а не как отформатированную строку.

I have some columns "UZT" and some "UZST" then getting error on "UZST"

Если это единственные часовые пояса, и вас не беспокоит разница в часах между двумя поясами, вы можете использовать:

SELECT TO_CHAR(
         TO_DATE(
           REPLACE( your_column, 'UZST', 'UZT' ),
           'Dy Mon DD HH24:MI:SS "UZT" YYYY'
         ),
         'Dy Mon YYYY'
       ) AS substring
FROM   your_table

или если вам нужно учитывать часовые пояса (замените значения часовых поясов соответствующим образом):

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ(
           REPLACE(
             REPLACE(
               your_column,
               'UZST',
               '+05:00'
             ),
             'UZT',
             '+05:00'
           ),
           'Dy Mon DD HH24:MI:SS TZH:TZM YYYY'
         ) AT TIME ZONE '+05:00',
         'Dy Mon YYYY'
       ) AS substring
FROM   your_table