SQL Reference - TRANSLATION

Example

SELECT TRANSLATE('12345', '143', 'ax') FROM DUAL;

Output

a2x5

Explanation

The TRANSLATE function in Oracle is used to replace a sequence of characters in a string with another set of characters. In the example provided, ‘143’ sequence is replaced by ‘ax’ in the string ‘12345’. The digit 1 is replaced with ‘a’, 4 is deleted as it does not have a replacement in the ‘ax’ string and 3 is replaced with ‘x’, giving the result ‘a2x5’.

Example

DECLARE @Example AS TABLE
(
    ID INT,
    Code VARCHAR(10)
)

INSERT INTO @Example
VALUES
    (1, 'C001'),
    (2, 'C002'),
    (3, 'C003')

DECLARE @Translation AS TABLE
(
    Code VARCHAR(10),
    Description VARCHAR(50)
)

INSERT INTO @Translation
VALUES
    ('C001', 'Code One'),
    ('C002', 'Code Two'),
    ('C003', 'Code Three')

SELECT E.ID, T.Description
FROM @Example E
JOIN @Translation T ON E.Code = T.Code

Output

ID  |Description
----------------
1   |Code One
2   |Code Two
3   |Code Three

Explanation

In the example, two table variables @Example and @Translation were declared with sample data. The JOIN SQL command was used to match and retrieve the Code’s description from @Translation for each row in @Example. The results include the ID from @Example and the corresponding Description from @Translation.