Oracle Remove part of string after a specific occurrence of a character
Why you are not trying this:
select SUBSTR('LUN_GGG_MAMA_FF_GG_GG_TT22_3',0, (INSTR ('LUN_GGG_MAMA_FF_GG_GG_TT22_3', '_', -1, 2)) - 1) from dual;
select SUBSTR('LORIK_BB_ID_FF_KKK_HUY_222_44',0, (INSTR ('LORIK_BB_ID_FF_KKK_HUY_222_44', '_', -1, 2)) - 1) from dual;
How to remove part of string in a column based on content of another column in Oracle
Try REGEXP_REPLACE
SELECT CLASSNUMBER,REGEXP_REPLACE(ClassNumber, '^'||Class, '') as id FROM t
Or if you are sure that it's always a single digit class, simply use
SUBSTR(CLASSNUMBER,2)
Demo
Sql to remove a substring within a string (oracle 11g)
Assuming that the substring to remove always is in the final part of the string, and that the substring 'New Account balance is Ksh'
at most occurs once in the string, you don't need regular expressions.
You can find the position where the string to remove starts (by INSTR
) and then trim the string up to that position (SUBSTR
):
select substr(yourString, 1, instr(yourString, 'New Account balance is Ksh') -1)
from ...
Removal of first characters in a string oracle sql
Just use substr()
:
select substr(col, 6)
This returns all characters starting at the sixth.
There are multiple ways to return all characters after the k
. If you know the string has a k
, then use instr()
:
select substr(col, instr(col, 'K') + 1)
Remove all characters after a specific character in PL/SQL
You can use SUBSTR
and INSTR
:
select substr('john.abc_1234', 1, instr('john.abc_1234', '_') -1)
from dual
Warning: This is only guaranteed to work if your string actually has an underscore in it
Update
Additionally, if you are running from Oracle 10g on, you could take the Regex path, which would more powerfully handle exceptions.
Here are some links on how to do it in Oracle:
- http://psoug.org/reference/regexp.html
- http://psoug.org/snippet/Regular-Expressions--Regexp-Cheat-Sheet_856.htm
- http://www.regular-expressions.info/oracle.html
remove specific word from string
Due to lack of support for lookbehind/lookahead and word boundary(\b
) in Oracle implementation of regular expression, it seems to be impossible to meet all requirements in single REGEXP_REPLACE
call. Especially for case, pointed out by Egor Skriptunoff : pattern matches, followed one by one with only one separator between them like some some some some ...
.
Without this case it's possible to match all such strings with this call:
regexp_replace(
source_string, -- source string
'([^[:alnum:]]|^)((\d)*some(\d)*)([^[:alnum:]]|$)', -- pattern
'\1\5', -- leave separators in place
1, -- start from beginning
0, -- replace all occurences
'im' -- case-insensitive and multiline
);
Pattern parts:
( -- start of Group #1
[^[:alnum:]] -- any non-alphanumeric character
| -- or
^ -- start of string or start of line
) -- end of Group #1
( -- start of Group #2
( -- start of Group #3
\d -- any digit
) -- end of Group #3
* -- include in previous group zero or more consecutive digits
some -- core string to match
( -- start of group #4
\d -- any digit
) -- end of group #4
* -- include in previous group zero or more consecutive digits
) -- end of Group #2
( -- start of Group #5
[^[:alnum:]] -- any non-alphanumeric character
| -- or
$ -- end of string or end of line
) -- end of Group #5
Because separators used for matching (Group #1 and Group #5) included in match pattern it will be removed from source string on successful match, so we need restore this parts by specifying in third regexp_replace
parameter.
Based on this solution it's possible to replace all, even repetitive occurrences within a loop.
For example, you can define a function like that:
create or replace function delete_str_with_digits(
pSourceString in varchar2,
pReplacePart in varchar2 -- base string (like 'some' in question)
)
return varchar2
is
C_PATTERN_START constant varchar2(100) := '([^[:alnum:]]|^)((\d)*';
C_PATTERN_END constant varchar2(100) := '(\d)*)([^[:alnum:]]|$)';
vPattern varchar2(4000);
vCurValue varchar2(4000);
vPatternPosition binary_integer;
begin
vPattern := C_PATTERN_START || pReplacePart || C_PATTERN_END;
vCurValue := pSourceString;
vPatternPosition := regexp_instr(vCurValue, vPattern);
while(vPatternPosition > 0) loop
vCurValue := regexp_replace(vCurValue, vPattern,'\1\5',1,0,'im');
vPatternPosition := regexp_instr(vCurValue, vPattern);
end loop;
return vCurValue;
end;
and use it with SQL or other PL/SQL code:
SELECT
delete_str_with_digits(
'some text, -> awesome <- 123 someone, 3some3
line of 7 :> some some some some some some some <
222some another some1? some22 text 0some000',
'some'
) as result_string
FROM
dual
SQLFiddle example
Remove string after second comma Oracle / PL SQL
You can use something very similar:
WITH s AS (SELECT '45465@6464@654' s FROM dual)
SELECT SUBSTR(s,1,INSTR(s,'@',1,2)-1) FROM s
or you can use regular Expression:
SELECT regexp_substr('45465@6464@654','([^@]*@)?[^@]*') from dual
Related Topics
Compare Strings Ignoring Accents in SQL (Oracle)
SQL Varchar(Max) VS Varchar(Fix)
How to Use a Pg Sequence on a Per Record Label
Interview - Detect/Remove Duplicate Entries
Multiple Rows into a Single Row and Combine Column SQL
Grant Create View on Oracle 11G
Sql: Finding the Closest Lat/Lon Record on Google Bigquery
SQL Split Column Based on 1 or More Possible Delimiter and Insert in New Table
Calculate the Last Day of the Prior Quarter
SQL Server 2008 - Conditional Query
Does Deleting Row from View Delete Row from Base Table - MySQL
Slick 3.0 - Update Columns in a Table and Return Whole Table Object
SQL Difference Between in and or in Where
Handling Unicode Characters That Aren't Displayed Correctly in SQL Query