Home » SQL & PL/SQL » SQL & PL/SQL » alphanumeric
alphanumeric [message #358379] Tue, 11 November 2008 01:15 Go to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
Hi,
How to validate the inserted value is alphanumeric?i want to restict the user to enter only alphanumeric.

Let me know if anyone has already done this.

Thanks
Re: alphanumeric [message #358382 is a reply to message #358379] Tue, 11 November 2008 01:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It can be done . But this kind of validation is preferred at the application level rather than at database level.

Smile
Rajuvan.
Re: alphanumeric [message #358401 is a reply to message #358379] Tue, 11 November 2008 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if your value must be a number then use a number column not a string one.

Regards
Michel
Re: alphanumeric [message #358403 is a reply to message #358379] Tue, 11 November 2008 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Only alphanumeric, as opposed to what?

Alphanumeric is genrally considered to by a-z, A-Z, 0-9 and the punctuation characters.

What keys could they press that don't come under your definition of alphanumeric?

Re: alphanumeric [message #358407 is a reply to message #358379] Tue, 11 November 2008 02:41 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,


do you mean to say some portion of the data only alpha (first three positions) and remaining (last foour positions) only numerals like ABC1234. is it the one you have mentioned as alphanumeric? if so, at application level using the format will serve your purpose.
yours
dr.s.raghunathan

Re: alphanumeric [message #358468 is a reply to message #358403] Tue, 11 November 2008 08:52 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rags11,
As rajauv1 posted its possible. One of the way is to implement together CHECK Constraints and REGEXP_LIKE Conditional Function (if you are using Oracle 10g or above).

@JRowbottom,

I am a bit confused with your comment here.
JRowbottom wrote on Tue, 11 November 2008 13:58

Alphanumeric is genrally considered to by a-z, A-Z, 0-9 and the punctuation characters.


I thought alphanumeric included only a-z, A-Z, 0-9. Does it really include punctuation characters. My first impression was OP wanted to avoid data with punctuation characters or special symbols. I am kind of a beginner in Oracle Regular Expressions and tried the following queries:

SQL>  SELECT 'ABCDE' Col_1
  2     FROM DUAL
  3    WHERE REGEXP_LIKE (';', '[ [:alnum:] ]');

no rows selected

SQL> SELECT 'ABCDE' Col_1
  2    FROM DUAL
  3   WHERE REGEXP_LIKE (';', '[ [:punct:] ]');

COL_1
-----
ABCDE

1 row selected.

As per your comment, the [:alnum:] character class should have passed the punctuation character(';') right? Can you or anyone else please guide me where I am going wrong or misinterpreting something?

Thanks,
Jo
Re: alphanumeric [message #358567 is a reply to message #358468] Tue, 11 November 2008 20:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
joicejohn wrote on Tue, 11 November 2008 09:52

I thought alphanumeric included only a-z, A-Z, 0-9.

Correct. Oracle's implementation of the alnum character class only covers a-z A-Z 0-9.
Re: alphanumeric [message #358912 is a reply to message #358468] Wed, 12 November 2008 23:52 Go to previous messageGo to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
I dont see the validation is happening here
If we select
SELECT 'ABCDE' Col_1
FROM DUAL
WHERE REGEXP_LIKE ('A1222', '[ [:alnum:] ]');


COL_1
-----
ABCDE

1 row selected.

SELECT 'ABCDE' Col_1
FROM DUAL
WHERE REGEXP_LIKE ('1222', '[ [:alnum:] ]');

COL_1
-----
ABCDE

1 row selected.



Re: alphanumeric [message #358929 is a reply to message #358567] Thu, 13 November 2008 00:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rags11
ebrian wrote on Wed, 12 November 2008 08:29
Oracle's implementation of the alnum character class only covers a-z A-Z 0-9.


Please understand the above quote. What are you trying to validate?

Regards,
Jo
Re: alphanumeric [message #358979 is a reply to message #358929] Thu, 13 November 2008 02:35 Go to previous messageGo to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
You mean A1222 or 222 or not in a-z A-Z 0-9???????
Re: alphanumeric [message #358983 is a reply to message #358379] Thu, 13 November 2008 03:00 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I dont see the validation is happening here

No, this is not validation; you were just shown how to create condition to distinguish it. By the way, the given mask check only the fact whether the expression contains at least one alphanumeric character.
SQL> WITH text AS (SELECT 'ABC-DEF' val FROM dual)
  2  SELECT val, CASE WHEN REGEXP_LIKE (val, '[ [:alnum:] ]') THEN 'alnum' ELSE 'not' END
  3  FROM text;

VAL     CASEW
------- -----
ABC-DEF alnum

1 row selected.

SQL> WITH text AS (SELECT 'ABC-DEF' val FROM dual)
  2  SELECT val, CASE WHEN REGEXP_LIKE (val, '^[ [:alnum:] ]+$') THEN 'alnum' ELSE 'not' END
  3  FROM text;

VAL     CASEW
------- -----
ABC-DEF not

1 row selected.

SQL> WITH text AS (SELECT 'ABCDEF' val FROM dual)
  2  SELECT val, CASE WHEN REGEXP_LIKE (val, '^[ [:alnum:] ]+$') THEN 'alnum' ELSE 'not' END
  3  FROM text;

VAL    CASEW
------ -----
ABCDEF alnum

1 row selected.

For validation, use check constraint, as suggested before.
Previous Topic: need back Ground Info
Next Topic: What should the column datatype be?
Goto Forum:
  


Current Time: Thu Dec 08 18:18:16 CST 2016

Total time taken to generate the page: 0.09071 seconds