Oracle 8, SQL: RTRIM for string manipulation is not working as expected

Question

Oracle 8 SQL: I do have data like "VTR 564-31 / V16 H12 W08 E19 L14" from which I want to trim the second part => "VTR 564-31"

According to this website I can use the rtrim function

rtrim('123000', '0'); would return '123'

like this it works, but adapted to my use case, the following one does not trim at all? Do I have to escape the special character???

rtrim('VTR 564-31 / V16 H12 W08 E19 L14',' / ')

Answer 1

RTRIM removes characters specified in the second parameter from the end of the string specified in the first. Since the last character of 'VTR 564-31 / V16 H12 W08 E19 L14' is a '4', which is not specified in the second parameter ' /', there is nothing to trim.

It looks like you think it looks for the first occurence of ' /' in the first string and removes everything from there on, but that's not what it does.

For example:

SQL> select rtrim('AAABBBCCCBBBAAA','AB') from dual;

RTRIM('AA
---------
AAABBBCCC

RTRIM removed all the As and Bs from the end of the string.

Probably what you actually want is:

select substr(yourstring, 1, instr(yourstring, ' / ')-1) from dual; 

i.e. use INSTR to locate the position of ' / ' and then SUBSTR to get just the part of "yourstring" before that.

Answer 2

What you want is something like:

SELECT RTRIM(SUBSTR('VTR 564-31 / V16 H12 W08 E19 L14',1,INSTR('VTR 564-31 / V16 H12 W08 E19 L14','/')-1),' ')
FROM DUAL;

The INSTR function locates the '/' in your string value, the SUBSTR function extracts the characters from the start of the string to the character immediately before the '/' located by INSTR, while the RTRIM removes any spaces that had occurred before the '/'