CODEWITHSUNDEEP
oracle-database
Abdul
Abdul

Reputation: 1486

data with specific length and characters in select query oracle

My table look like this in my oracle db;

ID | NI       | NT      | MB         |  ETC
-------------------------------------------
1  |1234567   |         |            | comments  //valid
2  |9654875   |         |            | jhdsd     //valid
3  |43gf543   |         |            | dd        //in-valid
4  |123       |         |            | dfds      //in-valid
5  |12654332  |         |            | dffd      //in-valid
6  |          |542      |            | comments  //valid
7  |          |362      |            | jhdsd     //valid
8  |          |9631     |            | dd        //invlaid
9  |          |r45      |            | dfds       //in-valid
10 |          |56       |            | dffd      // in-valid 
11 |          |         |03121234567 | comments  //valid
12 |          |         |03874514414 | jhdsd    //valid
13 |          |         |05764544444 | dd      //in-valid as not starts with 03
14 |          |         |30010101019 | dfds    //in-valid
15 |          |         |038f5678543 | dffd    //in-valid

I like select only valid records with select query

where

result should look like this;

1  |1234567   |         |            | comments  
2  |9654875   |         |            | jhdsd    
3  |          |542      |            | comments  
4  |          |362      |            | jhdsd    
5  |          |         |03121234567 | comments  
6  |          |         |03874514414 | jhdsd

Upvotes: 0

Views: 49

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

you could use a substr and length 

  select ID, NI, NT, MB, ETC
  from my_table  
  where length(NI) = 7 
  and length(NT) = 3 
  and substr(MB,1,2) ='03'
  AND REGEXP_LIKE(NI, '^[[:digit:]]+$')
  AND REGEXP_LIKE(NT, '^[[:digit:]]+$')

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

Try this:

  • NI length should be fix 7 and all, starts with any digit

    REGEXP_LIKE(NI, '^\d{7}$')

  • NT length should be fix 3 and all, starts with any digit

    REGEXP_LIKE(NT, '^\d{3}$')

  • digits MB length should be fix 11, starts with 03 and all digits.

    REGEXP_LIKE(MB, '^03\d{9}$')

Upvotes: 2

Related Questions