CODEWITHSUNDEEP
sqlregexoracle-database
Amit Soni
Amit Soni

Reputation: 11

Query not working properly(syntax issue may be)

I want to remove all non numeric characters from my columns, I have a query inside my procedure but it is not completely working. Here is my query:

UPDATE NMS_DATA SET ALIAS=REPLACE(ALIAS,'[^0-9]','') WHERE ALIAS !='--';

Non numeric characters are still there after query is fired from the procedure.

Upvotes: 1

Views: 32

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

Well, it is REGEXP_REPLACE you need, not REPLACE (at least, according to code you wrote).

For example:

SQL> with nms_data (alias) as
  2    (select 'abc993' from dual union all
  3     select '332#$'  from dual union all
  4     select '--'     from dual union all
  5     select 'xyz'    from dual union all
  6     select '1234'   from dual union all
  7     select '$#%'    from dual
  8    )
  9  select alias,
 10         regexp_replace(alias, '[^0-9]', '') result
 11  from nms_data
 12  where alias <> '--';

ALIAS  RESULT
------ --------------------
abc993 993
332#$  332
xyz
1234   1234
$#%

SQL>

Upvotes: 1

Related Questions