CODEWITHSUNDEEP
regexoracle-databaseregexp-replace
Craig Stevensson
Craig Stevensson

Reputation: 1336

Oracle REGEXP to replace only the first character

In the example below:

WITH X(DATA, ORD)
AS
(         
Select '@asdf@'   , 01 FROM DUAL UNION
Select '\qw@er\'  , 02 FROM DUAL UNION
Select '-zxcv-@' , 03  FROM DUAL UNION
Select '_poiu@' ,  04 FROM DUAL 
)

SELECT
REGEXP_REPLACE(DATA, '[@\-_]', '', 1)
FROM X
ORDER BY ORD;  
;

I get this response:

asdf 
qwer
-zxcv- 
poiu

But I would like to replace the "@", or the "\" or the "-" or the "_" only if it is the first character on the string, not in the middle. Plus it is not working for the "-" character.

Upvotes: 0

Views: 5642

Answers (2)

David Faber
David Faber

Reputation: 12495

The following regex should work: ^[@\\_-]. Note that the dash - must be the last character in the character class.

WITH X AS (
  SELECT '@asdf@' AS data, 01 AS ord FROM DUAL UNION
  SELECT '\qw@er\', 02 FROM DUAL UNION
  SELECT '-zxcv-@', 03  FROM DUAL UNION
  SELECT '_poiu@',  04 FROM DUAL 
)
SELECT REGEXP_REPLACE(DATA, '^[@\\_-]')
 FROM X
ORDER BY ORD

Upvotes: 2

Jean-François Savard
Jean-François Savard

Reputation: 21004

Instead, use this 

REGEXP_REPLACE(DATA, '^[@|\\|-|_]', '', 1)

Notice the ^ character which specify the begining of the string.

The query

WITH X(DATA, ORD)
AS
(         
Select '@asdf@'   , 01 FROM DUAL UNION
Select '\qw@er\'  , 02 FROM DUAL UNION
Select '-zxcv-@' , 03  FROM DUAL UNION
Select '_poiu@' ,  04 FROM DUAL 
)
SELECT
REGEXP_REPLACE(DATA, '^[@|\\|-|_]', '', 1)
FROM X
ORDER BY ORD;

Gave me the result

asdf@, qw@er, zxcv@, poiu@,

Upvotes: 1

Related Questions