CODEWITHSUNDEEP
sqloracle-databasesubstr
haoyun
haoyun

Reputation: 57

Oracle SQL SUBSTR function

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

Upvotes: 2

Views: 660

Answers (2)

AndyDan
AndyDan

Reputation: 759

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

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 3

Related Questions