Finding numbers in a varchar2 column

[oradba101]

Good morning, all

I have a column that is supposed to be used to put in a staff person's name. On occasion, the people doing the data entry will put in the staff person's id which consists of a 4-digit number. I would like to be able to identify these columns.

Regards,


William Chadbourne
Oracle DBA

 

[BJCooperIT]

If you create a function that tests for numerics (which is very handy and has been discussed at length in the TT fora), you could:

select * from STAFF_TABLE 
where F_IS_NUMERIC(STAFF_NAME) = 'Y'

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[BJCooperIT]

FYI - see: thread186-494811

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[Beilstwh]

Just an fyi, how about having the user pick from a list, then store the staff id. Having a free form name will always lead to mis-information and spelling mistakes. If you are using a form, you can use a reducing list so that as you type in the staff members name, the available list gets shorter and shorter.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

...people doing the data entry will put in the staff person's id which consists of a 4-digit number

I infer from your comment, as Bill did, that the Employee information already exists on your database.

As I you are already aware, you can avoid ever having this same problem recur by transforming your current (varchar) column into a declared Foreign Key column to the declared Primary Key column of your EMPLOYEE table. This solution prevents "bad data" ever existing inside your subject table.

Let bad data live as brief a life as possible.

In your case, your implementing the FK->PK-matching method can (in the future) prevent your current "bad data" from ever living at all.


I'm mentioning this here for the benefit of the casual/recreational Tek-Tipster that may see this thread and inappropriatly consider the "number-checking" alternative to be the "correct" solution to your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[oradba101]

Hi, all

Unfortunately, I actually have to move the data into Oracle. The system it comes from is written in dataflex using the native database that comes with it. I have never seen such a horrible system in my life and can say with honest sincerity that I know I shall never see a system as bad as this one for the rest of my life.

It has a form designer that makes is still stuck in the late 80's (and I'm being kind). When the person who designs forms here creates a new form, he has to use fields from pre-defined tables. There is no column or table level validation and the forms do not allow for any validation in them as well.

I have to submit a batch of records to the state and in order to avoid having a lot of them rejected, I am in the process of creating a PL/SQL procedure that will reject any records that are incorrect and send a report to the supervisors of the people doing data entry.

The view I have to create consists of a 41 table and view join. This is because the fields chosen to make up this batch are from the pre-defined tables I alluded to above.

I think your suggestion is a great idea and if I could do it I certainly would.

Regards,



William Chadbourne
Oracle DBA

 

[BJCooperIT]

My sympathies William. Dealing with legacy systems like that is a nightmare. A function will help do what you want, but I do not envy you the ensuing full table scans.

 

[SantaMufasa]

William,

As a "happy medium"-solution (since you cannot remedy the atrocious design) and to avoid the full-table-scan symptoms to which Barb refers, can you not do a one-time scrub of the data, then put a database trigger on the table to ensure that that field complies with your business/data rules.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[oradba101]

Hi, Mufasa

I'll look into doing just that. Normally, I have to load the data every night using a series of batch files and SQL*Loader scripts. Thank the Lord that I found a product that will read from any ODBC source and convert it to Oracle SQL*Loader scripts or this would be a lot worse.

Regards,


William Chadbourne
Oracle DBA