Silverterra 3d blog

Goal: to find all the rows with invalid order number.

We have a process where some outsider application send orders to a web page and read the answer from the server.
The answer sometimes is incorrect. The page structure is seemingly good, but we have incorrect data. For example the place of order number contains some other information or some strange text.

So I want to select all the rows where the order number (VARCHAR2) contains non numeric characters.
Here is a simple solution (let’s suppose we have only alphabetical characters):

1. simple solution (check only we have alphabeticals)

select *
from TA_KR_TRANSPORT a
where upper(web_rend) != lower(web_rend)

the result shows all the bad records

2. scientific solution (drop the numbers and check anything else)

select *
from TA_KR_TRANSPORT a
where length(replace(translate(web_rend, '0123456789', '0000000000'), '0', '' )) > 0

of course the result is the same (the running time is similar like before)

Done. We can fix the receiver code now to try to avoid the similar situations in the future.

Tuesday, June 3rd, 2014 at 17:26.
Filed under oracle.