Reputation: 661
Using ORACLE sql developer
I have:
Field ID 1: '006789'
Field ID 2: '026789'
Field ID 3: '126789'
I want:
Field ID 1: '6789'
Field ID 2: '26789'
Field ID 3: '126789'
This lives in table "EMPLOYEES"
I want to do something like this
begin
if FIELD_ID is like '00%' then
update EMPLOYEES
set FIELD_ID = %s/\%1c..//
elseif FIELD_ID is like '0%' then
update EMPLOYEES
set FIELD_ID = %s/\%1c.//
endif;
I'm really new to procedures and regex (obviously).
Upvotes: 0
Views: 616
Reputation: 3
I realize this is old, but another option (since the source data is a number with leading zeros) you could convert the string to a number and then back to a string something like: to_char(to_number(field_id))
Here's an example:
WITH TST_DATA AS (
SELECT '006789' FIELD_ID FROM DUAL UNION ALL
SELECT '026789' FIELD_ID FROM DUAL UNION ALL
SELECT '126789' FIELD_ID FROM DUAL
)
SELECT TO_CHAR(TO_NUMBER(FIELD_ID)) FIELD_ID
FROM TST_DATA
;
FIELD_ID
6789
26789
126789
Upvotes: 0
Reputation: 10525
If you need to remove leading or trailing characters from a string, you don't need regex, TRIM functions wil suffice. In Oracle, there are three functions, TRIM, LTRIM and RTRIM.
To answer your question, either
ltrim(field_id,'0')
or
trim(leading '0' from field_id)
should work.
Also, note that there a subtle difference between TRIM and LTRIM/RTRIM. TRIM can take only one trim character, while RTRIM/LTRIM can take many.
Upvotes: 3
Reputation: 1254
not sure why did you go with RegExp, but if you need just removing Leading and Trailing character from string Oracle has TRIM function for that.
TRIM(both '1' from '123Tech111') would return '23Tech'
example that fits your requirement
Upvotes: 1
Reputation: 6649
You can do it with a single update statement like,
UPDATE employees set field_id = ltrim(field_id, '0');
Upvotes: 1