Mobile Number should start from 3 in Oracle SQL and also check length should be 10

Question

I have mobile numbers in Oracle table column whose datatype is string

  3451111111
923452222222
 03451111211

I want SQL statement to select mobile numbers in this form 3451111113 only and check the length = 10.

I want a sub string that starts from character 3 and end at the end of string of length 10. It should neglect the 0, 92 at the beginning and start counting of string from 3 to onwards.

So - you should return only the last ten characters (ten digits), and only in the following situations: (1) The input is at least ten characters long; (2) The tenth digit counting from the **end** of the string is the digit 3. Do you know already that all the characters are digits, or can they be something else? (Like: parentheses, dash, spaces, perhaps letters, etc.) — , Oct 22 '20 at 19:10

score 1 · Answer 1 · answered Oct 22 '20 at 19:03

1

You can use regexp_like():

where regexp_like(number, '^(0|92)?3[0-9]{9}$')

This matches either 0, 92, or nothing at the beginning of the string, then a 3, and then 9 digits.

answered Oct 22 '20 at 19:03

Gordon Linoff

1,242,037
58
646
786

Thanks @Gordon for help. But regexp_like is giving error in oracle. But my issue has been resolve by substr(number, -10) – ali419 Oct 22 '20 at 19:13

score 1 · Answer 2 · answered Oct 22 '20 at 19:39

1

You can use LIKE:

SELECT *
FROM   table_name
WHERE  value LIKE '%3_________';

or, if you particularly want strings starting with 3, 03 or 923 then:

SELECT *
FROM   table_name
WHERE  value LIKE '3_________'
OR     value LIKE '03_________'
OR     value LIKE '923_________';

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT '3451111111' FROM DUAL UNION ALL
SELECT '923452222222' FROM DUAL UNION ALL
SELECT '03451111211' FROM DUAL UNION ALL
SELECT '3' FROM DUAL UNION ALL
SELECT '312345678' FROM DUAL UNION ALL
SELECT '3123456789' FROM DUAL UNION ALL
SELECT '31234567890' FROM DUAL;

Both output:

| VALUE        |
| :----------- |
| 3451111111   |
| 923452222222 |
| 03451111211  |
| 3123456789   |

db<>fiddle here

answered Oct 22 '20 at 19:39

MT0

143,790
11
59
117

The OP stated explicitly in words, and his example supports it, that he only wants the substring of length ten to be returned - not the entire input value. Whether that makes sense or not is another matter. :-) – Oct 22 '20 at 23:19
@mathguy The OP's last sentence says "It should neglect the 0, 92 at the beginning and start counting of string from 3 to onwards." Whether the OP just wants to check the last 10 characters or return them is ambiguous but it is a simple matter to change `SELECT *` to `SELECT SUBSTR( value, -10 ) AS phone_no` if they only want the last 10 characters returned. – MT0 Oct 22 '20 at 23:25

score 0 · Answer 3 · answered Oct 22 '20 at 19:04

0

To get the last 10 digits:

SUBSTR(phone_num,-10)

answered Oct 22 '20 at 19:04

dnoeth

59,503
4
39
56

score 0 · Answer 4 · answered Oct 23 '20 at 07:50

To select only values where the last 10 characters are all digits beginning with 3:

select substr(str,-10)
from   demo
where  regexp_like(str,'3\d{9}$');

The filter will exclude for example 30 bananas (the last 10 characters start with 3, but they are not all digits). It ignores any preceding characters, so for example banana3123456789 would be reported as 3123456789. It's not clear whether you want to exclude that as well.

regexp_substr(str,'3\d{9}$') gives you the portion of the string you want, if you just want to select it without filtering the results.

Mobile Number should start from 3 in Oracle SQL and also check length should be 10

4 Answers4