Philip Ebenezer, September 20, 2017 - 1:32 pm UTC
Appreciate your response. I will make sure my future questions are better version.
Not The Most Elegant
SeanMacGC, September 20, 2017 - 4:04 pm UTC
But will get the job done:
SELECT SUBSTR('13320A',1, REGEXP_INSTR('13320A','[^[:digit:]]')-1) "REGEXPR_SUBSTR" FROM DUAL;
REGEX
-----
13320
SELECT SUBSTR('131# A23',1, REGEXP_INSTR('131# A23','[^[:digit:]]')-1) "REGEXPR_SUBSTR" FROM DUAL;
REG
---
131
Or equally:
SELECT SUBSTR('131# A23',1, REGEXP_INSTR('131# A23','[^0-9]')-1) "REGEXPR_SUBSTR" FROM DUAL;
&
SELECT SUBSTR('13320A',1, REGEXP_INSTR('13320A','[^0-9]')-1) "REGEXPR_SUBSTR" FROM DUAL;
@SeanMacGC from Dublin
Stew Ashton, September 20, 2017 - 5:10 pm UTC
regexp_substr(<input>, '^\d+')
'^' means anchor at the beginning.
\d is Perl shorthand for digits.
+ means 1 to as many a possible.
regexp_substr will just return the part that matches the pattern.
Best regards, Stew Ashton
Thank you Stew Ashton
SeanMacGC, September 21, 2017 - 7:18 am UTC
Concise and elegant.
Reason?
Racer I., September 21, 2017 - 11:30 am UTC
I would submit that alle parts of a housenumber may be relevant. Nothing says is has to be an integer.
regards,
September 22, 2017 - 12:35 am UTC
Agreed.
Non Elegant Solution
Chuck Jolley, September 22, 2017 - 3:10 pm UTC
The times I've had to do something similar it has never worked well enough to do it elegantly. Too many clinkers.
It's been: "Get to know the data intimately and then write a custom function."
Especially if the data has been entered by hand.
I think it should work
Harshit, October 27, 2017 - 12:15 pm UTC
1 ) SELECT regexp_substr ('131# A23', '[0-9]+', 1, 1) RESULT
FROM dual;
RESULT
--------------------------------
131
2 ) SELECT regexp_substr ('539# APT 3 ', '[0-9]+', 1, 1) result
FROM dual;
RESULT
--------------------------------
539
3) SELECT regexp_substr ('339REAR ', '[0-9]+', 1, 1) result
FROM dual;
RESULT
--------------------------------
339
Note : Do correct me if its wrong
October 27, 2017 - 2:34 pm UTC
maybe not :-)
SQL> SELECT regexp_substr ('XA131# A23', '[0-9]+', 1, 1) RESULT FROM dual;
RES
---
131