Oracle / PLSQL: Test a string for an alphabetic value
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 function, TRIM function, and TRANSLATE function built into Oracle.
You can use the following command:
LENGTH(TRIM(TRANSLATE(string1, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
- string1
- 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', ' ')))
Result: 1
LENGTH(TRIM(TRANSLATE('Tech1Net2', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
Result: 2
LENGTH(TRIM(TRANSLATE('Tech on the Net', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
Result: null
Learn more about the TRANSLATE function.
Learn more about the LENGTH function.
Learn more about the TRIM function.
Advertisements