CODEWITHSUNDEEP
sqloracle-database
SQL_DE
SQL_DE

Reputation: 11

SUBSTR in sql oracle from right

I have data in table 000_ABC_AXEL. The expectation is that i have to exclude data after the last '_' and get 000_ABC in oracle sql? Any suggestions?

Need sql query to achieve below for ex:

a_222_4 -- > expected result :a_222
123_xyz_0 -- >expected result :123_xyz

Upvotes: 0

Views: 131

Answers (2)

MT0
MT0

Reputation: 167981

You can do it with simple string functions (which are much faster than regular expressions) by finding the sub-string up to the character before the last underscore:

SELECT SUBSTR(col, 1, INSTR(col, '_', -1) - 1) AS first_parts
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (col) AS
SELECT 'a_222_4'   FROM DUAL UNION ALL
SELECT '123_xyz_0' FROM DUAL;

Outputs:

FIRST_PARTS
a_222
123_xyz

fiddle

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

A regex replacement fits your requirement nicely:

SELECT col, REGEXP_REPLACE(col, '_[^_]+$', '') AS col_out
FROM yourTable;

Upvotes: 3

Related Questions