| Question about stripping out special characters from names [message #9940] |
Tue, 16 December 2003 05:57  |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
What I am doing is creating a procedure that will populate a table with a user name and will take the 1st character of the first name column and concatenate with the forst 7 characters of the last name...the problem is how I do I ignore the special chatacer's such as hyphens- apostrophes ' or ` commas.
Is there some sort of SQL command that would perhaps allow me to strip out any non alpha characters?
Or
any ideas on how I can do this in an automated way with my procedure...
Greatly appreciate the help...
|
|
|
|
| Re: Question about stripping out special characters from names [message #9941 is a reply to message #9940] |
Tue, 16 December 2003 06:59  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Andy,
In our shop, we use these two functions.SQL> CREATE OR REPLACE FUNCTION remove_all (
2 p_string IN VARCHAR2
3 , p_to_remove IN VARCHAR2
4 )
5 RETURN VARCHAR2
6 IS
7 l_cleaned VARCHAR2(32767);
8 BEGIN
9 IF (p_to_remove IS NULL) THEN
10 l_cleaned := p_string;
11 ELSE
12 l_cleaned := REPLACE(TRANSLATE(p_string
13 , p_to_remove
14 , SUBSTR(p_to_remove,1,1))
15 , SUBSTR(p_to_remove,1,1));
16 END IF;
17
18 RETURN (l_cleaned);
19 END remove_all;
20 /
Function created.
SQL> CREATE OR REPLACE FUNCTION remove_all_but (
2 p_string IN VARCHAR2
3 , p_to_keep IN VARCHAR2
4 )
5 RETURN VARCHAR2
6 IS
7 l_cleaned VARCHAR2(32767);
8 BEGIN
9 IF (p_to_keep IS NULL) THEN
10 l_cleaned := p_string;
11 ELSE
12 l_cleaned := REPLACE(TRANSLATE(p_string
13 , TRANSLATE(p_string
14 , p_to_keep
15 , RPAD(CHR(0)
16 , LENGTH(p_to_keep)
17 , CHR(0)))
18 , CHR(1))
19 , CHR(1));
20 END IF;
21
22 RETURN (l_cleaned);
23 END remove_all_but;
24 /
Function created.
SQL>
In the second function, only characters that appear in the second parameter are retained from the first parameter. We've found this function useful for standardizing phone numbers and stripping out oddball characters.SQL> SET DEFINE OFF
SQL> CREATE TABLE t (x VARCHAR2(30));
Table created.
SQL> INSERT INTO t VALUES ('Bob''s Emporium');
SQL> INSERT INTO t VALUES ('L!o@o#$k %t^h&r*o(u)g+h=');
SQL> INSERT INTO t VALUES ('résumé');
SQL> INSERT INTO t VALUES ('Call 212-555-1212');
SQL> INSERT INTO t VALUES ('(212) 555-1212');
SQL> INSERT INTO t VALUES ('212-555-1212');
SQL> INSERT INTO t VALUES ('212.555.1212');
SQL> INSERT INTO t VALUES ('212 /5551212');
SQL> INSERT INTO t VALUES ('Call area code (212)555-1212');
SQL> COMMIT;
Commit complete.
SQL> COLUMN just_letters FORMAT A30
SQL> COLUMN "JUST_#S" FORMAT A30
SQL> COLUMN no_5s_or_2s FORMAT A30
SQL> SELECT t.x
2 , remove_all_but(t.x,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 || 'abcdefghijklmnopqrstuvwxyz') just_letters
4 , remove_all_but(t.x,'0123456789') "JUST_#S"
5 , remove_all(t.x,'52') no_5s_or_2s
6 FROM t
7 /
X JUST_LETTERS JUST_#S NO_5S_OR_2S
------------------------------ ------------------------------ ------------------------------ ------------------------------
Bob's Emporium BobsEmporium Bob's Emporium
L!o@o#$k %t^h&r*o(u)g+h= Lookthrough L!o@o#$k %t^h&r*o(u)g+h=
résumé rsum résumé
Call 212-555-1212 Call 2125551212 Call 1--11
(212) 555-1212 2125551212 (1) -11
212-555-1212 2125551212 1--11
212.555.1212 2125551212 1..11
212 /5551212 2125551212 1 /11
Call area code (212)555-1212 Callareacode 2125551212 Call area code (1)-11
9 rows selected.
SQL>
Art.
|
|
|
|
|
|
|
|
| Re: Question about stripping out special characters from names [message #10519 is a reply to message #10513] |
Tue, 27 January 2004 23:25 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following example is only for the character conversion that you specified for one column of the table. You would need to create additional replace statements for each column and add additional replaces for additional characters to be replaced.
scott@ORA92> CREATE TABLE your_table
2 (col1 VARCHAR2(10))
3 /
Table created.
scott@ORA92> CREATE OR REPLACE TRIGGER your_table_bir
2 BEFORE INSERT ON your_table
3 FOR EACH ROW
4 BEGIN
5 :NEW.col1 := REPLACE (:NEW.col1, 'È', 'E');
6 END your_table_bir;
7 /
Trigger created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> INSERT INTO your_table VALUES ('È')
2 /
1 row created.
scott@ORA92> SELECT * FROM your_table
2 /
COL1
----------
E
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
