SQL Question

[tarek]

Hi ..
Could any body tell me the reason of this stranger thing which happened to me
We know that if we execute the following command :

SQL> select rtrim('users_id','_id')xxx from dual;

xxx
-----
users


Or

SQL> select rtrim('managers_id','_id')xxx from dual;

xxx
--------
managers


But why I have the following :


SQL> select rtrim('field_id','_id')xxx from dual;

xxx
----
fiel

Or :

SQL> select rtrim('fiels_is','_is')xxx from dual;

xxx
----
fiel


I note that the last letter in the end of the first string is the same with the last letter in the end of the first after changing …But in oracle documentation there are no things note that ..

Thanks ..

[jmodic]

There is nothing strange about this. It behaves exactly as described in manuals, check the manuals again.

SQL> select rtrim('fiels_is','_is')xxx from dual;
xxx
----
fiel

The second parameter to the RTRIM does not mean that all the occurances of the string contained in that parameter will be removed from the right side of the first parameter string. It means that every character contained in the second parameter string will be removed from the right side of the first parameter string (untill the first character not contained in the second parameter is found in the first parameter string).

So in your case it takes string 'fiels_is' and remove from it every character '_', 'i' and 's' from the right side until it finds the first character different from those three.

So all of the following three selects will return exactly the same result as your select above:

select rtrim('fielsis_','_is')xxx from dual;

select rtrim('fiels_is','s_i')xxx from dual;

select rtrim('fiels_is','_iszt234a?')xxx from dual;

select rtrim('fiels_isii_sss_i','_is')xxx from dual;

[tarek]

Thanks very much ( Jurij Modic ) , i understand exactly what you've explained to me ..And really thanks for your clear and detailed replay ..

Tarek