tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

Oracle/PLSQL: Test a string for an alphanumeric value

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

Answer: To test a string for alphanumeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))

string1 is the string value that you are testing

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

For example,

LENGTH(TRIM(TRANSLATE('Tech on the Net!!!', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' '))) would return 3
LENGTH(TRIM(TRANSLATE('Tech1Net2^', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' '))) would return 1
LENGTH(TRIM(TRANSLATE('Tech on the Net567', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' '))) would return null

Learn more about the TRANSLATE function.

Learn more about the LENGTH function.

Learn more about the TRIM function.