Kalyana Chakravarthy, May 11, 2016 - 9:17 am UTC
Hi,
Thanks for your swift response.
Regards
Kalyana
Kalyana Chakravarthy, May 11, 2016 - 9:26 am UTC
Hi,
Basically we are using this validation inside a PL/SQL,
Is it possible to give the string separately.
select regexp_like(str, '^[^a-zA-Z]*$') from dual;
is not working in sql dveloper.
we would like to have
select regexp_like(str, '^[^a-zA-Z]*$')
into from <table1>,<table2>;
Could you suggest?
Thanks
Kalyana
May 11, 2016 - 10:24 am UTC
select regexp_like(str, '^[^a-zA-Z]*$') from dual;
Fails because STR isn't a column in dual.
Regexp_like returns a boolean. If you want to use it in a SQL statement you have to use a case expression like in my example. You can't select the result of it into a variable.
I'm not sure what you're trying to do though - could you clarify please?
Specifically, what are you trying to achieve with:
select regexp_like(str, '^[^a-zA-Z]*$')
into from <table1>,<table2>;
Followup
Kalyana Chakravarthy, May 11, 2016 - 10:47 am UTC
Hi,
my requirement is as below:-
though its not exact code, pl note the validations in if condition.
create or replace TYPE C1_REC AS OBJECT
(
product_id is number
prod_value varchar2,
prod_type varchar2,
);
create or replace TYPE C1_TBL IS TABLE OF C1_REC;
create or replace procedure test_proc1
(p1_data in c1_tbl,
error_flag out varchar2)
is
begin
for i in 1..p1_data.count
if prod_value='A' then
p1_data(i).prod_type
--- condition1---
---check if the prod type is numeric
elsif prod_value='B' then
-- condition2 ---
-- check if the prod type is alpha numeric
----condition3---
elsif prod_value='C'
--- check if prod type is hexadecimal
-----------
end if;
end loop;
exception
end;
Please let me know further.
Regards
Kalyana
May 11, 2016 - 11:57 am UTC
If you want to use regexp_like in PL/SQL, place it in if or case statements. e.g.:
if regexp_like(p1_data(i).prod_type, '^[^g-zG-Z]*$') then
...
end if;
Kalyana Chakravarthy, May 11, 2016 - 1:33 pm UTC
Hi,
Thanks for the info .
If regexp_like (prod_type,'<pattern')
...
end if.
Is it possible to know what will be value for alphanumeric.
Thank you
regards
Kalyana
May 11, 2016 - 2:56 pm UTC
You can use character class :alnum: - see the comment below for more details.
Consider character classes
Thomas Brotherton, May 11, 2016 - 2:42 pm UTC
Instead of
if regexp_like(p1_data(i).prod_type, '^[^g-zG-Z]*$') then
...
end if;
you could use character classes like this
if regexp_like(p1_data(i).prod_type, '^[[:xdigit:]]+$') then
...
end if;
A good list of the character classes can be found here, and you have pre-built expressions for alphanumeric, digits, hex digits, and it already takes non English characters into account
http://www.regular-expressions.info/posixbrackets.html
May 11, 2016 - 2:57 pm UTC
Good point, thanks.
Varsha, November 03, 2017 - 6:31 pm UTC
If you add any character in the first string other than alphabet, still it displays as number
e.g. '-1,2.34*5!67890'
with vals as (
select '-1,2.34*5!67890' str from dual union all
select 'xyz123' str from dual union all
select '1234567890abcdef' str from dual
)
select str,
case
when regexp_like(str, '^[^1-9]*$') then 'pnumber'
when regexp_like(str, '^[^a-zA-Z]*$') then 'number'
when regexp_like(str, '^[^g-zG-Z]*$') then 'hex'
else 'string'
end typ
from vals;
Result:
STR TYP
-------------------------------
-1,2.34*5!67890 number
xyz123 string
1234567890abcdef hex
November 04, 2017 - 11:01 am UTC
The original regular expressions were just an example and they were chosen for the task of classification, not validation. They use the exclusion operator '^'. Blacklisting in validation is prone to omissions. Whitelisting is preferred.
Now, you need to decide the detailed syntax of your valid string content to be able to define a foolproof regular expression. In particular, by numeric or alphanumeric, do you mean Latin numeric and alphanumeric or any language numeric and alphanumeric? If you mean Latin, use:
numeric (positive integers only): '^[0-9]+$'
alphanumeric: '^[0-9A-Za-z]$'
hexadecimal: '^[0-9A-Fa-f]$'
If you mean "any language", which in case of an AL32UTF8 (Unicode) database really means almost ANY, use the character classes mentioned by Thomas. Note that there are digits in other scripts, like Arabic, so [0-9]+ and [[:digit:]]+ mean different things in Unicode.
Last note: for Latin regular expressions to work as above, NLS_SORT session parameter must be BINARY. Other values may include accented Latin characters between A and Z. In Oracle Database 12.2, you can protect yourself from this effect by saying REGEXP_LIKE (string COLLATE BINARY, 'pattern'). Unfortunately, this syntax works in SQL only. It is not yet implemented in PL/SQL.
Thanks
Yogesh Sati, October 03, 2018 - 3:20 am UTC
Not a big thing, just wanted to point out the explaination before the code has same string for Number and Hex
October 03, 2018 - 3:51 am UTC
Thanks
A similar case with a different context
Praveen B K, August 13, 2019 - 9:20 am UTC
Thanks for the valuable tips in this thread. Much appreciated.
I would like to however, add a point here.
I have seen instances of numeric data being stored in varchar2 columns (instead of number). Now, if that column has even one record which isn't numeric in nature, then the implicit conversion fails and we end up with the ORA-01722: Invalid Number error.
I was searching for a way to find out which record was the culprit here and stumbled upon this thread. This is how I did it.
select * from <TABLE_NAME> where REGEXP_LIKE(<COLUMN_NAME>,'\D');
August 13, 2019 - 10:12 am UTC
Thanks for sharing.
Better Performance.
Korbinian, August 28, 2019 - 7:08 am UTC
I try to avoid regexp whenever possible, because it is slow. I need to use the expression to probably check about 1 million strings a day, wether they are valid hex-strings.
This is what i would do for testing for hex in the example above:
with vals as (
select '-1,2.34*5!67890' str from dual union all
select 'xyz123' str from dual union all
select '1234567890abcdef' str from dual
)
select str,
case
when regexp_like(str, '^[^1-9]*$') then 'pnumber'
when regexp_like(str, '^[^a-zA-Z]*$') then 'number'
when REPLACE(translate(str, '1234567890ABCDEFabcdef',' '),' ','') is null then 'hex'
else 'string'
end typ
from vals;
This way I also exclude a load of special characters I do not want to have in my Hex-Strings. I prefere explicitly listing all valid characters to listing characters not allowed, it prevents surprises. It sure comes with the draw back of being quite strict.
To reproduce results from above case use:
with vals as (
select '-1,2.34*5!67890' str from dual union all
select 'xyz123' str from dual union all
select '1234567890abcdef' str from dual
)
select str,
case
when regexp_like(str, '^[^1-9]*$') then 'pnumber'
when regexp_like(str, '^[^a-zA-Z]*$') then 'number'
when length(REPLACE(translate(UPPER(str), 'GHIJKLMNOPQRSTUVWXYZ',' '),' ','')) = length(str) then 'hex'
else 'string'
end typ
from vals;
Sry I have not modeled the other tests to non regular expression.
August 29, 2019 - 10:16 am UTC
Yep, as Serguisz says above, whitelisting & being strict is better for validation. You need to test thoroughly with your allowed and disallowed characters.
For example, these regexes classify punctuation as "pnumber" and numbers including punctuation as "number":
with vals as (
select '-1,2.34*5!67890' str from dual union all
select 'xyz123' str from dual union all
select '1234567890abcdef' str from dual union all
select '@' str from dual
)
select str,
case
when regexp_like(str, '^[^1-9]*$') then 'pnumber'
when regexp_like(str, '^[^a-zA-Z]*$') then 'number'
when length(REPLACE(translate(UPPER(str), 'GHIJKLMNOPQRSTUVWXYZ',' '),' ','')) = length(str) then 'hex'
else 'string'
end typ
from vals;
STR TYP
-1,2.34*5!67890 number
xyz123 string
1234567890abcdef hex
@ pnumber
display number, characyter & Special characters
Vijeta Nirapure, May 21, 2021 - 7:03 am UTC
I/p : EMP table
Name
1
2 2
3
a
b
c
$
#
o/p :-
name String
1 number
2 number
3 number
a alphabets
b alphabets
c alphabets
$ special character
# special character
Query:-
select name, case (name)
when regexp_substr (name,'[[:alpha:]]',1,1)
then 'alphabets'
when regexp_substr (name,'[[:digit:]]',1,1)
then 'number '
else 'Special Chracter'
end String
from Emp;
May 21, 2021 - 12:14 pm UTC
What's your question?
Pattern matching
Nayeema, October 26, 2021 - 3:18 pm UTC
Hi Tom,
How do I find out spaces between alphanumeric strings?
validate_conversion alternative
Johan Snyman, October 27, 2021 - 11:54 am UTC
A good way to validate input is to try to convert it to the date type you want to validate it conforms to.
From 12c, Oracle provides the validate_conversion function for this, e.g.
select validate_conversion('21313' as number) from dual;
will return 1
select validate_conversion('a' as number) from dual;
will return 0
Can also be used with format masks, e.g. to check for hex:
select validate_conversion('A21D' as number, 'XXXX') from dual
will return 1
select validate_conversion('A21G' as number, 'XXXX') from dual
will return 0
Typically more direct and less prone to error than those regular expressions.
October 28, 2021 - 2:54 am UTC
Agreed.
Also CAST and the TO_NUMBER/DATE functions have similar extensions.
PLSQL
sudha, June 09, 2022 - 3:39 am UTC
I/p : EMP table
Name
1
2
3
a
b
c
$
#
o/p :-
name String
1 number
2 number
3 number
a alphabets
b alphabets
c alphabets
$ special character
# special character
SQL> select ename, case (ename)
2 when regexp_substr (ename,'[[:alpha:]]',1,1)
3 then 'alphabets'
4 when regexp_substr (ename,'[[:digit:]]',1,1)
5 then 'number '
6 else 'Special Chracter'
7 end String
8 from Emp;
ENAME STRING
---------- ----------------
BLAKE Special Chracter
JONES Special Chracter
SCOTT Special Chracter
FORD Special Chracter
SMITH Special Chracter
20 Special Chracter---------Showing still special character
WARD Special Chracter
MARTIN Special Chracter
JAMES Special Chracter
9 rows selected.
what's the wrong with above query ?
June 09, 2022 - 2:09 pm UTC
I don't understand what's going on here.
Please provide a test case in the form of:
- create table
- insert into that_table
- the query you're running
- the output you expect