How to print a contact number leaving country code and '-','/' and print the rest of contact number using regular expressions in sql?

Question

I would like to print only contact number leaving country code aside using regular expressions. Let us assume a table with 2 contacts

PHONENO
-----------
+89-8646538468
+0222-4684653453465

EXPECTING OUTPUT:

PHONENO
----------------
8646538468
4684653453465

SELECT Phoneno, SUBSTR(REGEXP_REPLACE(Phoneno,'-',''),5) FROM Phone_no; — Bharath_Raja, Apr 12 '18 at 09:53
dublicate of https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql — Sergiu Costas, Apr 12 '18 at 09:56
@user8795734 MySQL has no built in support for regex replacement/extraction. You will need to either use the base string functions, or use/create a UDF. — Tim Biegeleisen, Apr 12 '18 at 09:58
Yayyyyy!! Got that guys using regular expressions. I am unable to find an answer button here. can I comment — Bharath_Raja, Apr 12 '18 at 11:56
SELECT Phoneno ,SUBSTR(Phoneno,REGEXP_INSTR(Phoneno,'-')+1) FROM Phone_no; — Bharath_Raja, Apr 12 '18 at 11:57

score 2 · Accepted Answer · answered Apr 12 '18 at 09:55

2

This is very easy using MySQL's SUBSTRING_INDEX function:

SELECT
    PHONENO AS full_number,
    SUBSTRING_INDEX(PHONENO, '-', -1) AS short_number
FROM yourTable;

Demo

answered Apr 12 '18 at 09:55

Tim Biegeleisen

Can you please help me write a code with regular expression as well. – Bharath_Raja Apr 12 '18 at 10:04
1

I guess you missed my comment above. You **can't** extract using regex in MySQL, because there is no such function. – Tim Biegeleisen Apr 12 '18 at 10:05
Yayy!! Thanks guys. Got that using regular expressions only.Here is the code followed :- SELECT Phoneno ,SUBSTR(Phoneno,REGEXP_INSTR(Phoneno,'-')+1) FROM Phone_no; – Bharath_Raja Apr 12 '18 at 11:58
can you help me in making my question valid. I am new here, so please. – Bharath_Raja Apr 12 '18 at 12:02
please upvote my question bro – Bharath_Raja Apr 12 '18 at 12:11
I upvoted a long time ago already. – Tim Biegeleisen Apr 12 '18 at 12:26
Please post my comment as answer bro! I am unable to post an answer – Bharath_Raja Apr 12 '18 at 12:28