You Asked

Hi,

I have a form which has a field. This field must be alphanumberic(only letters&numbers permitted). I permit the end user to input the other "special characters", but internally i want to strip them out first and keep only the leftover as my clean data key which will be based on to continue process the transaction.

E.g.: sk6789er$2%%&6(before) ====> sk6789er26(after)

I doubt we have the same function "IS_ALPHA_NUNERIC()" function in C or C++ language, so what function or procedure we can use and how?

Thanks for you kindness.

and Tom said...

tkyte@TKYTE816> create table t ( x varchar2(25) );

Table created.

tkyte@TKYTE816>
tkyte@TKYTE816> set define off
tkyte@TKYTE816> insert into t values ( '*sk6789er$2*%%&6' );

1 row created.

tkyte@TKYTE816>
tkyte@TKYTE816> select replace(translate( x,
2 translate( x,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
3 rpad( '*', 62, '*' ) ), ' ' ), ' ', '')
4 from t
5 /

REPLACE(TRANSLATE(X,TRANS
-------------------------
sk6789er26

the inner translate turns all "good" characters into '*'. so, we end up with (for this string):

*********$*%%&*

so, the outer translate is now:

translate( '*sk6789er$2*%%&6', '*********$**%%&*', ' ' )

that returns:

' sk6789er2 6'

now we use replace to get rid of the blanks and are left with what we want.

follow up to third comment below:

the 62 is the length of ABC...Zabc....z01....9 -- NOT the length of the string X. the rpad('*',62,'*') makes a translation table that is the length of the input translation table.

Rating

(9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reader, May 20, 2001 - 3:48 pm UTC

Helena Markova, May 21, 2001 - 2:41 am UTC

Replace 62 by Length Function

Asif Momen, May 21, 2001 - 2:46 am UTC

Hi Tom,

The SQL is wonderful. But, why to hardcode the statement by '62', instead of that we can read the length of the column eg. Length(x).

select replace(translate( x, translate( x,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rpad( '*', Length(x), '*' ) ), ' ' ), ' ', '')
from t
/

Regards,

Momen.

Special Characters

Mark M, December 18, 2002 - 2:26 pm UTC

Space between characters in a string

Ratan, August 21, 2003 - 3:43 am UTC

Hi,
Need help to write query to get the following.

my input: "Testing"
how to get this Output : " T e s t i n g"
i.e. each character follwed by sapce except last character.

Thanks in advance.
Ratan

ailuro, April 29, 2005 - 3:29 am UTC

SQL> select regexp_replace('Testing','(.)',' \1') PRESPACEDCHARS from dual;

PRESPACEDCHARS
--------------
 T e s t i n g

9I solution for insert a Space between characters in a string

Frank Zhou, January 18, 2006 - 5:19 pm UTC

Here is a 9I solution:

SQL> SELECT MAX(SYS_CONNECT_BY_PATH (ch , ' '))  new_str
  2    FROM (
  3             SELECT  SUBSTR(IN_STR, LEVEL , 1) ch , ROWNUM rn
  4               FROM  (SELECT 'Testing' AS IN_STR FROM dual)
  5            CONNECT BY  LEVEL <= LENGTH(IN_STR)
  6         )
  7    START WITH rn = 1
  8    CONNECT BY  PRIOR rn = rn -1;

NEW_STR
--------------------------------------------------------------------------------
 T e s t i n g

SQL> spool off

Frank

what about unicode?

Thomas, September 05, 2006 - 4:48 am UTC

Hi Tom,

his works fine for US-Letters, but how to code this using only unicode chars? To build a string of all valid letters is no option as it is for building a string of all invalid letters - they are just too many.

is there a simple solution (for 9i)?

September 05, 2006 - 5:01 pm UTC

you have to srot of define what are valid "characters" to you - there is no "valid characters" function, no.

10G regular expression solution for the original question

Frank Zhou, January 09, 2007 - 5:23 pm UTC

Here is a 10G solution using regular expression .

Thanks,

Frank

SQL> select str str_old,
2 REGEXP_REPLACE( str, '[^[:alnum:]]' , '') as st_new
3 from t;

STR_OLD ST_NEW
------------------------------ ------------------------------
*sk6789er$2*%%&6 sk6789er26

Breadcrumb

Question and Answer