How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..