How To Get Rows Not Containing Numbers

Today a developer ask me about how to get rows, using like filter a alphanumeric column (varchar2), but he need get all rows not having numbers, where say number I refer from zero to nine. Is a good question, it is possible with like clause but tedious. The naturally solution maybe is using regular expressions, Oracle (from 10g version) include the regexp_like function. The problem in this case is because regexp_like look for string characters that match with the pattern we are looking. In this example we are looking only stringts made of characters only, numbers are excluded. A example of this is the netx sql sentence;

                                                             
SELECT COUNT (*) 
FROM DBA_SEGMENTS 
WHERE 
TABLESPACE_NAME = 'WORK_TBS' AND 
REGEXP_LIKE (SEGMENT_NAME, '[a-zA-Z]') ;

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s