Take out last 2 characters from the string and append at the beginning of the string

Question

I have a requirement where from the string, I need to take out the last 2 characters and append them at the beginning of the string.

Example: FM73457 2038 is the string value. I want to take the last 2 char i.e. 38 from FM73457 2038 and append it to start i.e. 38FM73457 and remove space and 20.

The output should be like 38FM73457

Is there another way to do it apart from concat?

select substr('FM73457 2038',11,2)||substr('FM73457 2038',1,7) from dual;

38FM73457

Answer 1

Is there another way to do it apart from concat?

You could use regular expressions:

SELECT REGEXP_REPLACE(value, '^(.*) \d\d(\d\d)$', '\2\1') AS replaced
FROM   table_name;

But its quicker to use simple string functions:

SELECT SUBSTR(value, -2) || SUBSTR(value, 1, 7) AS replaced
FROM   table_name

or:

SELECT SUBSTR(value, -2) || SUBSTR(value, 1, LENGTH(value) - 5) AS replaced
FROM   table_name

or:

SELECT SUBSTR(value, -2) || SUBSTR(value, 1, INSTR(value, ' ') - 1) AS replaced
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'FM73457 2038' FROM DUAL;

All output:

REPLACED
38FM73457

fiddle