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.

Answer 1

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.

Answer 2

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.

Answer 3

To get the last 10 digits:

SUBSTR(phone_num,-10)

Answer 4

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