CODEWITHSUNDEEP
sqlregexoracle-database
newbie
newbie

Reputation: 155

Get only Number

How to ignore special characters and get only number with the below input as string.

Input: '33-01-616-000' Output should be 3301616000

Upvotes: 0

Views: 80

Answers (4)

Art
Art

Reputation: 5782

SELECT regexp_replace('33-01-616-000','[^0-9]') digits_only FROM dual; 
/

Upvotes: -1

eifla001
eifla001

Reputation: 1157

You can also use REGEXP_REPLACE function. Try code below,

SELECT REGEXP_REPLACE('33-01-61ASDF6-0**(98)00[],./123', '([^[:digit:]])', NULL) 
  FROM DUAL;

Upvotes: 0

user5683823
user5683823

Reputation:

Standard string functions (like REPLACE, TRANSLATE etc.) are often much faster (one order of magnitude faster) than their regular expression counterparts. Of course, this is only important if you have a lot of data to process, and/or if you don't have that much data but you must process it very frequently.

Here is one way to use TRANSLATE for this problem even if you don't know ahead of time what other characters there may be in the string - besides digits:

TRANSLATE(columnname, '0123456789' || columnname, '0123456789')

This will map 0 to 0, 1 to 1, etc. - and all other characters in the input string columnname to nothing (so they will be simply removed). Note that in the TRANSLATE mapping, only the first occurrence of a character in the second argument matters - any additional mapping (due to the appearance of the same character in the second argument more than once) is ignored.

Upvotes: 0

Barmar
Barmar

Reputation: 780724

Use the REPLACE() function to remove the - characters.

REPLACE(columnname, '-', '')

Or if there can be other non-numeric characters, you can use REGEXP_REPLACE() to remove anything that isn't a number.

REGEXP_REPLACE(columnname, '\D', '')

Upvotes: 5

Related Questions