Hi fellas,
In oracle development, sometime we have to extract only the numbers from the given string. If the given string is has a dynamic length we can use TRIM for this task. But we have select whether it is LTRIM or RTRIM. Just imagine we have a string like '-4646'. We have to extract the numbers without '-'. We can use below statement for that.
select LTRIM('-4646','-') from dual;
If the unwanted part of the string ('-' in the example) is in left side, we can use LTRIM. If it's on the right side, we can use RTRIM.
Just imagine we have a string like '---00045454' and we have to extract only '45454' from the string. Also the length of this string can be change. Its dynamic. Apart from that the number of numbers in the string can be change and the number of '-' s and '0' can be change. In this kind of a situation we can use TRIM like below.
select LTRIM ( LTRIM ( '---00045454' , '-' ) , '0' ) from dual;
You can use both LTRIM and RTRIM like below for some situations.
select RTRIM ( LTRIM ( '----4587000' , '-' ) , '0' ) from dual;
You can use number of times of trims in your statement accordingly. The major thing you should consider is that you should give the characters you want to delete in a proper order. Otherwise it wont give the real output you want. The other thing you should consider about is select the trim you should use according to the places of the characters (RTRIM or LTRIM).
You can use trim within trim in your statements. In those kind of situations,the most inner trim will trim the value accordingly and pass the resulted value to the previous trim. This trim will trim the value accordingly and pass the value to his previous trim and so on and on. You can use this function with REG_EXP_REPLACE simultaneously.
TRIM function is really suitable for the situations like
In oracle development, sometime we have to extract only the numbers from the given string. If the given string is has a dynamic length we can use TRIM for this task. But we have select whether it is LTRIM or RTRIM. Just imagine we have a string like '-4646'. We have to extract the numbers without '-'. We can use below statement for that.
select LTRIM('-4646','-') from dual;
If the unwanted part of the string ('-' in the example) is in left side, we can use LTRIM. If it's on the right side, we can use RTRIM.
Just imagine we have a string like '---00045454' and we have to extract only '45454' from the string. Also the length of this string can be change. Its dynamic. Apart from that the number of numbers in the string can be change and the number of '-' s and '0' can be change. In this kind of a situation we can use TRIM like below.
select LTRIM ( LTRIM ( '---00045454' , '-' ) , '0' ) from dual;
You can use both LTRIM and RTRIM like below for some situations.
select RTRIM ( LTRIM ( '----4587000' , '-' ) , '0' ) from dual;
You can use number of times of trims in your statement accordingly. The major thing you should consider is that you should give the characters you want to delete in a proper order. Otherwise it wont give the real output you want. The other thing you should consider about is select the trim you should use according to the places of the characters (RTRIM or LTRIM).
You can use trim within trim in your statements. In those kind of situations,the most inner trim will trim the value accordingly and pass the resulted value to the previous trim. This trim will trim the value accordingly and pass the value to his previous trim and so on and on. You can use this function with REG_EXP_REPLACE simultaneously.
TRIM function is really suitable for the situations like
- String has a dynamic length.
- Number of unwanted characters can be change by time to time.
- Both starting point and ending point includes unwanted characters.
Trim couldn't filter all the characters. But he will do somethings for you when he fit to the situation.
No comments:
Post a Comment