Home » Developer & Programmer » Forms » how to insert a character value with out numbers (merged)
how to insert a character value with out numbers (merged) [message #328706] Sun, 22 June 2008 00:26 Go to next message
karunakar_adepu84
Messages: 43
Registered: January 2008
Member
which means if we create a column with the varchar2 datatype in a table it will accept only characters I know, but if we insert like example : ' ka12434'

this it will also takes the value. But My Question is It will not take the numbers in that.
please reply me soon.
regards,
Re: How to Create a table without accapting numbers [message #328708 is a reply to message #328706] Sun, 22 June 2008 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to create a trigger for this.

Regards
Michel
Re: How to Create a table without accapting numbers [message #328751 is a reply to message #328706] Sun, 22 June 2008 11:11 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
If you mean you want to prevent the numeric characters 0-9 from being entered, a check constraint might also be helpful, e.g:

CREATE TABLE test_table
( somecol  VARCHAR2(30)
           CONSTRAINT somecol_no_numeric_chars_chk
                      CHECK (somecol = TRANSLATE(somecol,'1234567890','')) );

Re: How to Create a table without accapting numbers [message #328752 is a reply to message #328751] Sun, 22 June 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was my first thought but OP said:
Quote:
but if we insesrt like example : ' ka12434'
this it will also takes the value. But My Question is It will not take the numbers in that.

The value is correct but must be inserted without the numbers, so a check constraint can't do it.

Regards
Michel
Re: How to Create a table without accapting numbers [message #328753 is a reply to message #328706] Sun, 22 June 2008 11:31 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As question in its title mentions 10g version, there's an easy way to do that using regular expressions.

Constraint that will take care of such a problem looks like this:
SQL> CREATE TABLE Test
  2    (COL VARCHAR2(20) CHECK (NOT REGEXP_LIKE(COL, '[[:digit:]]')));

Table created.

SQL> INSERT INTO test (col) VALUES ('abcd');

1 row created.

SQL> INSERT INTO test (col) VALUES ('abcd1234');
INSERT INTO test (col) VALUES ('abcd1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0016148) violated


SQL> INSERT INTO test (col) VALUES ('1122abcd');
INSERT INTO test (col) VALUES ('1122abcd')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0016148) violated


SQL> INSERT INTO test (col) VALUES ('12345');
INSERT INTO test (col) VALUES ('12345')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0016148) violated
Re: How to Create a table without accapting numbers [message #328754 is a reply to message #328753] Sun, 22 June 2008 11:34 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, while I was creating an example, drinking coffee and chatting, you people have discussed it already. After re-reading the whole thing, I have a doubt: do you, Michel and Karunakar, say that 'ka12434' should be inserted as 'ka' (i.e. we should remove all numbers from an input string)? Or are we supposed to reject any string that contains numbers?
Re: How to Create a table without accapting numbers [message #328756 is a reply to message #328754] Sun, 22 June 2008 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
do you, Michel and Karunakar, say that 'ka12434' should be inserted as 'ka'

This is how I understood his sentence.

Regards
Michel
Re: How to Create a table without accapting numbers [message #328827 is a reply to message #328754] Mon, 23 June 2008 00:11 Go to previous messageGo to next message
karunakar_adepu84
Messages: 43
Registered: January 2008
Member
Thank You littlefoot,
I just want to know that record must to be reject by sql. If the version is oracle 9i what to do?
please reply me soon.
how to insert a character value with out numbers [message #328831 is a reply to message #328706] Mon, 23 June 2008 00:30 Go to previous messageGo to next message
karunakar_adepu84
Messages: 43
Registered: January 2008
Member
Here is my question,
I have developed one form based on one database table,
In the database table I have three columns with datatype varchar2.
My form is based on these three columns also. When I am inserting the values from form it will also taking the numerical values, but I want that the form must take only characters. If we try to insert numbers It has to disply some message. How to do?

regards,
karunakar
Re: How to Create a table without accapting numbers [message #328840 is a reply to message #328827] Mon, 23 June 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your question are really not clear.
Do you want the row to be rejected?
Do you want to value to be modified?
Do you want to know which row will be rejected?
What tool/language do you use?
In what situation are you?
How do you insert?
From where?

Explain soon in details.

Regards
Michel

[Updated on: Mon, 23 June 2008 00:59]

Report message to a moderator

Re: how to insert a character value with out numbers [message #328842 is a reply to message #328831] Mon, 23 June 2008 01:09 Go to previous messageGo to next message
cjack
Messages: 22
Registered: June 2008
Junior Member
Hi,

you can using below procedure in the WHEN-VALIDATE-ITEM to validate whether there are some numberic in the field.

PROCEDURE validate_field(p_text VARCHAR2) AS
v_flag NUMBER := 0;
BEGIN
IF p_text IS NOT NULL THEN
FOR i IN 1 .. lengthb(p_text) LOOP
IF ascii(substr(p_text,
i,
1)) BETWEEN 48 AND 57 THEN
v_flag := v_flag + 1;
END IF;
END LOOP;
END IF;
IF v_flag = 0 THEN
NULL;
ELSE
message('You type a numerical into a characters field!');
END IF;
EXCEPTION
WHEN OTHERS THEN
message(SQLERRM);
message('NULL');
END;
Re: How to Create a table without accapting numbers [message #328917 is a reply to message #328840] Mon, 23 June 2008 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I've seen he has opened a new topic in Forms forum; now the question isn't "how to create a table", but "how to write a form procedure" which will take care of a problem.

Problem? What problem? We still didn't identify it.
Re: How to Create a table without accapting numbers [message #328946 is a reply to message #328917] Mon, 23 June 2008 08:27 Go to previous messageGo to next message
karunakar_adepu84
Messages: 43
Registered: January 2008
Member
I am using form 6i I have a form with three columns the datatype is varchar2.
If I am trying to insert numeric value in the textfields Then I want to get an alert like " Please insert only characters, your inserted numeric values".
Re: How to Create a table without accapting numbers [message #337117 is a reply to message #328946] Wed, 30 July 2008 00:24 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Search this forum for 'iznumeric'.

David
Previous Topic: Updating a data block
Next Topic: How to get Forms to perform UPDATE not INSERT
Goto Forum:
  


Current Time: Thu Dec 08 00:38:55 CST 2016

Total time taken to generate the page: 0.10289 seconds