CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user466534
user466534

Reputation:

select from database according to phone number

Guys in a few days I have an exam (midterm), so I am trying to execute every possible test which could be in midterm, suppose that we have employees table where phone number is given in this form abc.edf.ghi (all these are numbers not strings), how could I list these employees who's phone number contains edf? I dont think that we can use substring function because it is number, am I correct? Also I can't execute division by sum number like in c++(division by 100,mod 100 and so on,)so please help me, sorry if my question is too much nonsense. I need just for practice.

Upvotes: 0

Views: 124

Answers (3)

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

Oracle says substr is for string only, but you can use substr for columns that are defined as NUMBER datatype,too.

(Assumed that the format of PHONENUMBERCOLUMN is abc.edf.ghi)

select * from YOURTABLE where substr(PHONENUMBERCOLUMN,5,3)='edf'

NOTE: It should be 'edf', not edf, if the datatype is other than NUMBER. Always use single quotation marks to be on the safe side, whether the datatype of the column is number or string (char,varchar,varchar2 etc.)

Upvotes: 1

Jan
Jan

Reputation: 11726

Don't make a mistake of thinking that phone number is a number. Phone number is a string which usually consists mainly of digits.

Imagine a phone number beginning with some zeros. If you'd store it as a number they will be truncated while they might be important and might make a difference. Storing it as a string makes it secure.

Additionally, storing it as a string enables you to use SUBSTR and other functions named similarly and doing more or less the same thing, which should solve your problem.

Upvotes: 1

Aditya Naidu
Aditya Naidu

Reputation: 1380

In MySQL you can try the SUBSTRING() function. If the phone number is in the format you show it has to be a varchar not a INT.

For Oracle use substr()

Upvotes: 0

Related Questions