How to remove part of the string in oracle How to remove part of the string in oracle sql sql

How to remove part of the string in oracle

Use the REPLACE method

Select REPLACE('abcdef_fhj_viji.dvc','abcde','')

If you want this query for your table :

Select REPLACE(column,'abcde','') from myTable

For update :

UPDATE TABLE   SET column = REPLACE(column,'abcde','') 

select substr('abcdef_fhj_viji.dvc',instr('abcdef_fhj_viji.dvc','_')+1) from dual

So, Its all depends on INSTR function, define from which position and which occurrence, you will get the index and pass that index to SUBSTR to get your string.

Since you didn't give a lot of information I'm gonna assume some.

Let's assume you want a prefix of some string to be deleted. A good way to do that is by using Regular Expressions. There's a function called regexp_replace, that can find a substring of a string, depending on a pattern, and replace it with a different string. In PL/SQL you could write yourself a function using regexp_replace, like this:

function deletePrefix(stringName in varchar2) return varchar2 isbegin  return regexp_replace(stringName, '^[a-zA-Z]+_', '');end;

or just use this in plain sql like:

regexp_replace(stringName, '^[a-zA-Z]+_', '');

stringName being the string you want to process, and the ^[a-zA-Z]+_ part depending on what characters the prefix includes. Here I only included upper- and lowercase letters.