Question: I have an application where I
need to ensure that my end-users don’t use special characters in the
name columns for the full name of a person.
How can I validate the format of a name in Oracle?
Answer: To validate first name and last
name fields from an Oracle application (APEX or SQL*Forms) the
regular expression regexp_like can be used to ensure that
the name follows the proper pattern. In general, English surnames
are all alphanumeric, with a dash allowed for hyphenated names.
For American names you want to validate:
- That the name is at least two characters long. There was a
famous case of a Vietnamese man with a one-letter last name who
was forced to change his name to two characters to accommodate
the validation routine of a motor vehicle database validation.
- That the name contains alpha characters plus a single,
optional dash (hyphenated names)
- That the name does not start with a hyphen
The regular expression syntax in PL/SQL might look like this,
using the regexp_like function:
If not regexp_like (input_first_name,
'^[a-zA-Z–]+$') then . . .
Or in PL/SQL you can declare the regex string:
SET SERVEROUTPUT ON
DECLARE
v_name varchar2(20) := '^[a-zA-Z–]+$' --- Only
alpha, space and dashes
BEGIN
If not
regexp_like(last_name,v_name)
then
dbms.output.put_line('Invalid surname'); . . .
END
You can also use regular expressions to validate names at data
entry time within your application, ensuring that only valid name
characters are entered:
if
not regexp_like (first_name, '^[a-zA-Z
.''-]+$')
or
not regexp_like (middle_name, '^[a-zA-Z
.''-]+$')
or
not regexp_like (last_name, '^[a-zA-Z
.''-]+$')
then
dbms_output.put_line(‘Invalid
characters in names. Please re-enter’);
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
