Home » SQL & PL/SQL » SQL & PL/SQL » how can I make Mobile column ?
icon5.gif  how can I make Mobile column ? [message #302178] Sun, 24 February 2008 12:03 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

we here in Egypt have specific format for mobiles number ..... it's like that :-

0129231239
0109283746
0168483873

ect,.

so .... I want create table with mobile column whither this column be ten numbers only no more no less , and first two numbers be (01)

I did that code but didn't work !

create table mob ( Mobile varchar2(10) check (length(mobile)='10' , substr(mobile,1,2)='01');


what's the right code for what I need ?!

and thanks in advance
Re: how can I make Mobile column ? [message #302179 is a reply to message #302178] Sun, 24 February 2008 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ '10' is a string not a number
2/ Use AND operator
3/ "Didn't work" is not an Oracle message, copy and paste what you have
4/ Count your parenthesis

Regards
Michel

[Updated on: Sun, 24 February 2008 12:14]

Report message to a moderator

Re: how can I make Mobile column ? [message #302180 is a reply to message #302178] Sun, 24 February 2008 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
insert into MOB (MOBILE) VALUES ('012ABCDEF3');
icon5.gif  Re: how can I make Mobile column ? [message #302184 is a reply to message #302180] Sun, 24 February 2008 14:39 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

Quote:
insert into MOB (MOBILE) VALUES ('012ABCDEF3');

good note
really I want all what I insert be only numbers not characters Sad

untill now I did that :-

create table mobile ( 
Mobile varchar2(10) 
check (length(mobile)='10' and substr(mobile,1,2)='01'));


but that code didn't solve character inserting problem Sad

any Help Question Exclamation Exclamation
Re: how can I make Mobile column ? [message #302185 is a reply to message #302184] Sun, 24 February 2008 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TRANSLATE and LENGTH.
There are also many examples of these here.

And '10' is still not a number.

Regards
Michel

[Updated on: Sun, 24 February 2008 14:42]

Report message to a moderator

Re: how can I make Mobile column ? [message #302187 is a reply to message #302184] Sun, 24 February 2008 15:28 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hany Freedom
I want all what I insert be only numbers not characters

+
Hany Freedom
and first two numbers be (01)

= won't work.

Numbers with a leading zero are stored without it. So, either use a character column (and you'll be able to store '01'), or let it be a numeric datatype column, but you'll have to format it for display purposes in order to show a leading zero.
icon13.gif  that all you have ??!!!!! [message #302194 is a reply to message #302178] Sun, 24 February 2008 16:30 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I want good answers Mad
check out this link ... they answer better than you Laughing
Re: how can I make Mobile column ? [message #302195 is a reply to message #302178] Sun, 24 February 2008 16:35 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Please post the solution that satisfies your requirements.
Re: that all you have ??!!!!! [message #302234 is a reply to message #302194] Mon, 25 February 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"better" is subjective.
For you it seems it means "spoonfed", here we (mostly) prefer "OP found it by himself with our help".

Regards
Michel
icon6.gif  mmmmm [message #302331 is a reply to message #302234] Mon, 25 February 2008 04:44 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

again ....... I'm still waiting for more better answers Cool
Re: mmmmm [message #302333 is a reply to message #302331] Mon, 25 February 2008 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Better than what? Better in which direction?

Regards
Michel
Re: mmmmm [message #302335 is a reply to message #302331] Mon, 25 February 2008 04:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Your previous post seems to imply that you already have been fed the answer that you were looking for from another forum. Why are you continuing to look for an answer here? Or could it be that the 'better' answers don't actually meet your requirements?

[Updated on: Mon, 25 February 2008 04:50]

Report message to a moderator

icon2.gif  well ..... [message #302357 is a reply to message #302335] Mon, 25 February 2008 05:38 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I'm still don't find what I want ..... but when I referred to that Link
I were mean they answer better than you .... they give me many ideas of what I want ..... that's right all that ideas not what I need exactly ... but at least they answer quickly and with many examples .... and that what I hope you do here in that forum Smile
Re: well ..... [message #302359 is a reply to message #302357] Mon, 25 February 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And in what the link you posted don't answer to your question?
Where are you stuck?
What do you already have?

Regards
Michel
Re: well ..... [message #302367 is a reply to message #302359] Mon, 25 February 2008 06:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
IMHO,
Spoon-feeding is an understatement here.
Because even it requires some level of effort, understanding what is been fed and atleast some appreciation.
As of now, the OP has failed to express these here and in the other esteemed forum.
The only other option I would usually recommend is to hire
a nanny to tell bed time stories about oracle and may be bottle-fed?
Gents, time to use the ignore option.
icon4.gif  well ..... [message #302385 is a reply to message #302359] Mon, 25 February 2008 07:37 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

until now that what I did :-

create table mobile ( 
Mobile varchar2(10) 
not null unique , 
check (length(mobile)='10' , check ( mobile between '0100000000' and '0199999999'));


but that not true because you can insert letters in the column
..... I want code make like that what I wrote but solve the inserting letters problem.
Re: well ..... [message #302389 is a reply to message #302385] Mon, 25 February 2008 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the answer has been given to you in the other site.
And for the third time: '10' is a string and not a number.

After all this you didn't move a single inch forward. You even added a syntax error.

Regards
Michel

[Updated on: Mon, 25 February 2008 07:51]

Report message to a moderator

Re: well ..... [message #302394 is a reply to message #302385] Mon, 25 February 2008 07:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I think you need to read the concepts manual on the difference between a character string and a number. You have been told two times, and you still do not know the difference.
Re: how can I make Mobile column ? [message #302397 is a reply to message #302178] Mon, 25 February 2008 08:12 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

OK ....... string string string string string string string string

are you happy now ?

the important now , where the code that carry out what I need Rolling Eyes Neutral Question Question Exclamation Exclamation Exclamation
Re: how can I make Mobile column ? [message #302398 is a reply to message #302178] Mon, 25 February 2008 08:17 Go to previous messageGo to next message
kakolz
Messages: 4
Registered: November 2007
Junior Member
Check this out:
create table mobile ( 
    Mobile varchar2(10) 
    not null unique, 
    constraint length_con check (length(mobile)='10') ,
    constraint start_with_con check (substr(mobile,1,2)='01'),
    constraint num_only_con check (
        length(trim(translate(mobile,'0123456789','          '))) = 0)
    );
Re: how can I make Mobile column ? [message #302399 is a reply to message #302398] Mon, 25 February 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'10' is still a string and not a number.

Regards
Michel
Re: how can I make Mobile column ? [message #302400 is a reply to message #302397] Mon, 25 February 2008 08:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hany Freedom wrote on Mon, 25 February 2008 15:12
OK ....... string string string string string string string string

are you happy now ?

the important now , where the code that carry out what I need Rolling Eyes Neutral Question Question Exclamation Exclamation Exclamation

How much do you get paid to do this? Since you seem to be beyond asking us for help, but rather demanding an answer, you might as well start paying for it.
Re: how can I make Mobile column ? [message #302401 is a reply to message #302398] Mon, 25 February 2008 08:25 Go to previous messageGo to next message
kakolz
Messages: 4
Registered: November 2007
Junior Member
Embarassed
I focused on adding the final constraint and just copy and paste an older version. Of course, you're right. It was an oversigth!

length(mobile)=10

create table mobile ( 
    Mobile varchar2(10) 
    not null unique, 
    constraint length_con check (length(mobile)=10) ,
    constraint start_with_con check (substr(mobile,1,2)='01'),
    constraint num_only_con check (
        length(trim(translate(mobile,'0123456789','          '))) = 0)
    );

Re: how can I make Mobile column ? [message #302419 is a reply to message #302178] Mon, 25 February 2008 10:17 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

thanks kakolz , your code is true
and this code is true too :-
create table mobile ( Mobile varchar2(10) not null unique ,
check (length(mobile)='10' and substr(mobile,1,2)='01'
and substr(mobile,3,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,4,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,5,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,6,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,7,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,8,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,9,1) in ('0','1','2','3','4','5','6','7','8','9')
and substr(mobile,10,1) in ('0','1','2','3','4','5','6','7','8','9')) );

and this code also true :-
create table mobile (
Mobile varchar2(10)
not null unique ,
check (length(mobile)=10 and
       to_number(mobile) between 100000000 and 199999999)
      );


that's all , thanks for you all , and I hope you benefit from me ... Laughing Laughing Laughing Laughing
Re: how can I make Mobile column ? [message #302423 is a reply to message #302419] Mon, 25 February 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, but both are wrong.
Just try it and you get a syntax error.

Regards
Michel

[Edit: missing word]

[Updated on: Mon, 25 February 2008 10:49]

Report message to a moderator

icon14.gif  Re: how can I make Mobile column ? [message #302426 is a reply to message #302423] Mon, 25 February 2008 10:36 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I have been tested the both of those codes that I already wrote ........ and they completely true.

what exactly the error that appear with you Question Exclamation Question Exclamation
Re: how can I make Mobile column ? [message #302428 is a reply to message #302426] Mon, 25 February 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, Embarassed I am wrong, I took part of your previous message (where there was a "," instead of "and").

Regards
Michel
Re: how can I make Mobile column ? [message #302441 is a reply to message #302428] Mon, 25 February 2008 12:16 Go to previous message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Length and first two digits are quite obvious. However, regular expressions might help in avoiding letters and provide a neat code:
CREATE TABLE mob (mobile VARCHAR2(10) 
                  CHECK (REGEXP_INSTR(mobile, '[[:alpha:]]') = 0)
                 );
Previous Topic: wrm$ views
Next Topic: Extract long Raw columns to file
Goto Forum:
  


Current Time: Mon Dec 05 10:46:13 CST 2016

Total time taken to generate the page: 0.09390 seconds