CODEWITHSUNDEEP
oracle-database
eldhoseeldhose
eldhoseeldhose

Reputation: 53

Can't trim the string in Oracle

I have a string IN-123456; now I need to trim the IN- from that string. I tried as in Oracle

select trim('IN-' from 'IN-123456) from dual;

but I get an error

ORA-30001: trim set should have only one character
30001. 00000 -  "trim set should have only one character"
*Cause:    Trim set contains more or less than 1 character. This is not
           allowed in TRIM function.

How can I solve this?

Upvotes: 0

Views: 3688

Answers (5)

Bodhisatwa Bhattacharya
Bodhisatwa Bhattacharya

Reputation: 1

Trim Function is always remove one char from string

Here is the example - SELECT TRIM(both 'P' FROM 'PHELLO WORLDP') FROM DUAL

Out put -HELLO WORLD

You may use LEADING /TRAILING insert of Both. In your case "IN-" holding three char.

Upvotes: 0

Gomzy
Gomzy

Reputation: 431

you just forget to complete single quote

select trim('IN-' from 'IN-123456') from dual;

now try this

Upvotes: 0

eldhoseeldhose
eldhoseeldhose

Reputation: 53

Thanks for the result...

It can be solved with LTRIM() function

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753615

Clearly, TRIM is not the correct function for the job. You need to REPLACE the (sub)string IN- with nothing:

SELECT REPLACE('IN-123456', 'IN-') FROM dual;

Be aware that this will replace all occurrences of IN- anywhere in the string. If that's not appropriate, but the IN- will always be at the start of the string, then you could use SUBSTR instead:

SELECT SUBSTR('IN-123456', 4) FROM dual;

Upvotes: 0

Plínio Almeida
Plínio Almeida

Reputation: 63

A simple replace wouldn't do the trick?

select replace('IN-123456', 'IN-', '') from dual;

Upvotes: 2

Related Questions