PLSQL 如何判断一个字符为字母

 

regexp_instr('字符','[[:alpha:]]') 如果为字母，则返回１，否则返回0 。 或通过ascii('字符')的值在某一区间进行判断。 TRANSLATE结合length也可以．

 

Question:  In Oracle, I want to know if a string value contains alphabetic characters only. How can I do this?

 

Answer:  To test a string for alphabetic characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))

string1 is the string value that you are testing

This function will return a null value if string1 is alphabetic. It will return a value "greater than 0" if string1 contains any non-alphabetic characters.

 

For example,

LENGTH(TRIM(TRANSLATE('Tech3', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))	would return 1
LENGTH(TRIM(TRANSLATE('Tech1Net2', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))	would return 2
LENGTH(TRIM(TRANSLATE('Tech on the Net', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))	would return null