CODEWITHSUNDEEP
sqlregexoracle-database
lostinthebits
lostinthebits

Reputation: 661

pl/sql conditionally replace characters of field in a table using regex

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

Answers (4)

TheDean
TheDean

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

Noel
Noel

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

Anup Shah
Anup Shah

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

Oracle Documentation

Upvotes: 1

Dba
Dba

Reputation: 6649

You can do it with a single update statement like,

UPDATE employees set field_id = ltrim(field_id, '0');

Upvotes: 1

Related Questions