Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

question about regular expression

ben512Nov 12 2007 — edited Nov 14 2007
Hi,

I have a column SAMPLE_ID in table TEST1.

The values in this column for example are

SAMPLE_ID
test_100_1_1
test_12_2_3
test_1_10_1
test_1_10_15

I would like to cut all characters beginning with the third underscore ('_') from the rest of the strings.
It should looks like this one:

SMAPLE_ID
test_100_1
test_12_2
test_1_10
test_1_10

I think I have to work with regular expressions and tried something but I can not find a solution.
Can anybody help me, please?


Thanks,
ben

Comments

Mike Kutz

Did you check to see if the syntax is valid already?

select
   trim( '01234567890', '0') x
from dual,
iwis

Of course I checked it. Both Oracle 19c on https://livesql.oracle.com/ and Oracle 18c on https://dbfiddle.uk/?rdbms=oracle_18&fiddle=5294d7089cb5552ca2fdf89c62e9bab4 raises the ORA-00907: missing right parenthesis error.

iwis

I have added this proposal in the "ideas" section: https://community.oracle.com/tech/developers/discussion/4479151/add-an-optional-argument-c-after-a-comma-in-the-trim-s-function/p1?new=1

Solomon Yakobson

You said you compared different database engines:
Oracle trim('0' from '01234567890')
SQL Server trim('0' from '01234567890')
MySQL trim('0' from '01234567890')
Postgres trim('0' from '01234567890')
All the above database engines have same TRIM syntax allowing leading, trailing or both (default):
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
SY.

iwis

All the above database engines have same TRIM syntax allowing leading, trailing or both (default):
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
Unfortunately, this syntax doesn't work in SQL Server. The select TRIM(LEADING '0' FROM '01230') query raises:

Msg 102 Level 15 State 1 Line 1
Incorrect syntax near '0'.

See: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9f1441351df23937f8866eb4edb32bd9

Solomon Yakobson

Right, SQL Server TRIM trims on both sides only (we must use LTRIM/RTRIM to trim on one side). But what this has to do with your proposal? Neither of database engines you listed except SQLite supports TRIM('string','characters') syntax so it should be SQLite where TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) syntax should be proposed.
SY.

iwis

Neither of database engines you listed except SQLite supports TRIM('string','characters') syntax
PostgreSQL also supports it (db<>fiddle). Additionally, as I said, it looks strange that Oracle database supports LTRIM(s, c) and RTRIM(s, c), but doesn't support: TRIM(s, c).
it should be SQLite where TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) syntax should be proposed.
It should be proposed in SQLite and SQL Server. I have done it here and here.

Solomon Yakobson

It should be proposed in SQLite and SQL Server. I have done it here and here.
Good, so when SQL Server & SQLite will implement it then TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) will be fully portable.
SY.

1 person found this helpful
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 12 2007
Added on Nov 12 2007
8 comments
2,480 views