| Initcap with First Name, Last Name and Address [message #47355] |
Wed, 11 August 2004 13:42  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Ok, here's my dilemma. One of our departments in the school is worried about instructors entering in students information in all lower or upper case. I know that I can use the initcap() function to initialize the first character of each string but what about instructors that correctly enter McDonald, PO BOX, 111 NE Main and so on. That's my dilemma.
Has anyone encountered this and what did you do? Anyone know about any tables that can be loaded that will check for last name spellings and address spellings? Or possibly, some code along with some tables that can scan this type of information. Maybe a table with all the possibilities of first name, last name and address.
What do credit card companies, or any company, for that matter, do when they accept on-line applications?
|
|
|
|
| Re: Initcap with First Name, Last Name and Address [message #47356 is a reply to message #47355] |
Wed, 11 August 2004 14:43  |
Andrew
Messages: 144
Registered: March 1999
|
Senior Member |
|
|
Unfortunately you'll need to handle those one by one. Do the initcap() and then cleanup any exceptions you know of.
...
v_txt VARCHAR2 (1000) := i_txt;
BEGIN
-- trick: Paste into Excel to sort
v_txt := REPLACE (v_txt, 'Ca ', 'CA ');
v_txt := REPLACE (v_txt, ',Ne ', ',NE ');
v_txt := REPLACE (v_txt, ',N ', ',N ');
v_txt := REPLACE (v_txt, ',Se ', ',SE ');
v_txt := REPLACE (v_txt, ',S ', ',S ');
v_txt := REPLACE (v_txt, ' Mac', ' MaC');
...
Unfortunately even these rules aren't specific enough for Mac to end up as MaC... The use of whitespace to identify the begining and end of words breaks down if your word is at the beginning or end of a line so watch for that. 10g pl/sql supports regular expressions which is more powerful for identifying paterns - maybe try to find some regular expression formatting logic and then just run that from pl/sql.
See 8.0.x web toolkit code:
OWA_PATTERN.MATCH
OWA_PATTERN.AMATCH
OWA_PATTERN.CHANGE
OWA_PATTERN.GETPAT
and
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2200894550208
|
|
|
|
| Re: Initcap with First Name, Last Name and Address [message #47357 is a reply to message #47355] |
Wed, 11 August 2004 15:06 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Try just applying initcap to data which is exclusively lowercase:SQL> create table initcap_tst(col1 varchar2(50));
Table created.
SQL> insert into initcap_tst values('This McDonald is OK, 123 NE South St.'); <<-- update won't touch this row
1 row created.
SQL> insert into initcap_tst values('this mcdonald is not ok, 123 ne south st.');
1 row created.
SQL> update initcap_tst
2 set col1 = initcap(col1)
3 where lower(col1) = col1;
1 row updated.
SQL> select * from initcap_tst;
COL1
--------------------------------------------------
This McDonald is OK, 123 NE South St. <<-- original format preserved
This Mcdonald Is Not Ok, 123 Ne South St. |
|
|
|
| Re: Initcap with First Name, Last Name and Address [message #47360 is a reply to message #47357] |
Thu, 12 August 2004 05:34 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Thanks Andrew, I was thinking along those same lines. It's still a pain. I would think someone would have a table or something that I could go against.
Do you know of a which to determine if data is exclusively lowercase or uppercase. I ask because if the department decides not to use the initcap() for the elements then it would be nice to determine if an instructor is entering everything in uppercase. If they are, then I can throw up an error. Most likely I will do the initcap() and then do what you are showing me. I just hope I can come up with all the possibilities that a first or last name could have.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
