I have a table like below:
Last_name First_name
aaaa bbb -
aaa bbbb -
aaa gggg j
How can I use substr function to delete the - in the first_name and keep the letter if it is not a -?
Thanks
Add a comment
|
2 Answers
You can use rtrim() function, specifying as a second parameter character(s) that you want to remove from the right-hand side of a string:
SQL> with t1(Last_name, First_name) as(
2 select 'aaaa', 'bbb -' from dual union all
3 select 'aaa', 'bbbb -' from dual union all
4 select 'aaa', 'gggg j' from dual
5 )
6 select last_name
7 , rtrim(first_name, ' -') as first_name
8 from t1
9 ;
Result:
LAST_NAME FIRST_NAME
--------- ----------
aaaa bbb
aaa bbbb
aaa gggg j
Starting from Oracle 10g version, you could also use regexp_replace() regular expression function:
select last_name
, regexp_replace(first_name, '\s*-\s*$') as first_name
from t1
Result:
LAST_NAME FIRST_NAME
--------- ----------
aaaa bbb
aaa bbbb
aaa gggg j
Comments
If you really want to use SUBSTR, try this:
with t1(Last_name, First_name) as(
select 'aaaa', 'bbb -' from dual union all
select 'aaa', 'bbbb -' from dual union all
select 'aaa', 'gggg j' from dual
)
select last_name
, substr(first_name, 1, decode(instr(first_name,' -'), 0, length(first_name), instr(first_name,' -') -1)) as first_name
from t1
;
Result:
LAST_NAME FIRST_NAME
--------- ----------
aaaa bbb
aaa bbbb
aaa gggg j
