Oracle query with field filter

Applications Programming
1

Dear community,
I have to make a query from a database and apply the following filter:

select filed1,field2,field3 
from database 
where (field1 contains only alphanumeric chars and NOT only numbers)

Let me explain better what I Need:

21test ==> OK
test ==> OK
test21 ==> OK
te21st ==> OK
12345 ==> Exclude it

So in other words, I need every value that contains:

  • letters
  • letters + number
  • NOT ONLY numbers

I believe REG_EXP can help me, something like:

where REGEXP_LIKE (field1,'^[a-zA-Z]*$')

But in this case I believe only letters will be filtered.

Thanks
Lucas

2

To have at least one letter somewhere in your field, try

"^.*[A-Za-z].*$"

EDIT: Sorry, misread your post. Try

"^[[:alnum:]]*[A-Za-z][[:alnum:]]*$"

but remove the windows style <CR> line terminators first!

1 Like
3

Hello Lord Spectre,

Following may help you in same. It is just an example.

echo "121221qdsdwd dejcw23132c" | awk '{if($1 ~ /^[[:digit:]]*$/){next} else {print $0}}'

Output will be as follows.

121221qdsdwd dejcw23132c

So it is printing only those lines whose 1st field not have only digits.

Thanks,
R. Singh

1 Like
4

Thanks both, but I have to implement the expression directly on query and not in post processing. Anyway since I have a large ammount of data, I'll try:

REGEXP_LIKE (field1,^[[:alnum:]]*[A-Za-z][[:alnum:]]*$)

And the reporting back when the query has finished.

---------- Post updated at 07:04 AM ---------- Previous update was at 05:53 AM ----------

This works perfect

REGEXP_LIKE (field1,^[[:alnum:]]*[A-Za-z][[:alnum:]]*$)

Thanks RudiC :b:

5

Not sure if that would work, but ain't it better (in terms of readability and simplicity) to invert the whole thing and use RavinderSingh13's regex :confused: E.g.

NOT REGEXP_LIKE (field1,^[[:digit:]]*$)
6

That would allow for e.g. punctuation or whitespace chars, which are unwanted (see post#1).

7

Now if going with classes, chase away the A-Za-z in favor of [:alpha:]?