Get only Number

Question

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

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

refer the below link https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — Ramkumar Sambandam, Apr 04 '18 at 21:52
Refer the below link [Query to get only numbers from a string][1] [1]: https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — Ramkumar Sambandam, Apr 04 '18 at 21:53

score 5 · Accepted Answer · answered Apr 04 '18 at 21:29

5

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', '')

answered Apr 04 '18 at 21:29

Barmar

741,623
53
500
612

@GordonLinoff People downvote answers when they think the question doesn't deserve an answer, to punish us for encouraging poor questions. – Barmar Apr 04 '18 at 21:48
1

@Barmar - I am actually convinced that most of the "drive-by" downvoters who do not leave a short note to explain their action have no reason at all. (Perhaps they just don't like people with high reputations.) Some may have a reason like the one you mentioned, but I believe those are in the minority. – Apr 04 '18 at 22:45

score 0 · Answer 2 · answered Apr 04 '18 at 22:43

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.

score 0 · Answer 3 · answered Apr 05 '18 at 01:24

0

You can also use REGEXP_REPLACE function. Try code below,

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

answered Apr 05 '18 at 01:24

eifla001

1,137
8
8

score -1 · Answer 4 · answered Apr 05 '18 at 15:29

-1

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

answered Apr 05 '18 at 15:29

Art

5,616
1
20
22

What does your answer add to those previously posted? – mustaccio Apr 05 '18 at 17:24
@mustaccio - it adds simplicity and common sense. This is the shortest answer. And if you are good at this then do not assume that OP does not want to know different ways to solve the problem. I;d save all answers in my book and try them all. And try not wasting your and others time. Try helping others instead. Good luck. – Art Apr 05 '18 at 17:29
I do not want any clarifications or anything at all. All I know is the query returns the expected result. How is this not provide the answer to the question? Stop being jealous and ridiculous guys ))). And please leave me alone. )) – Art Apr 05 '18 at 18:30

Get only Number

4 Answers4