Replace with previous alphabet in a given string-CodePudding

I'm in need of an Oracle-SQL query.

I have a string $F$ and here is a situation where i have to replace this with the previous alphabet.

Expected output: $E$

This string can contain any alphabet from A-Z. Alphabet A will not be available so if it is $Z$ then the output should be $Y$

Any help?

CodePudding user response：

You can use CHR and ASCII:

SELECT value,
       REGEXP_REPLACE(
         value,
         '\$([B-Z])\$',
         '$'
         ||CHR(ASCII(REGEXP_SUBSTR(value,'\$([B-Z])\$',1,1,NULL,1))-1)
         ||'$'
       ) AS replaced_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'BCDEF$Z$GHI' FROM DUAL;

Outputs:

VALUE REPLACED_VALUE

BCDEF$Z$GHI BCDEF$Y$GHI

VALUE	REPLACED_VALUE
BCDEF$Z$GHI	BCDEF$Y$GHI

If you just have 3-character strings in your table then:

SELECT value,
       '$' || CHR(ASCII(SUBSTR(value,2,1))-1) || '$' AS replaced_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '$Z$' FROM DUAL

Outputs:

VALUE REPLACED_VALUE

$Z$ $Y$

VALUE	REPLACED_VALUE
$Z$	$Y$

db<>fiddle here