CODEWITHSUNDEEP
regexoracle-databaseplsqloracle11g
PacificNW_Lover
PacificNW_Lover

Reputation: 5376

How to validate City Name using RegExp in PL/SQL

Am using Oracle 11g as my database and PL/SQL as the SQL language to validate certain columns inside a table.

Requirement(s):

  1. Validate a "City Name" so that it only contains alpha characters and no numbers.

  2. Can contain a space for cities such as San Jose or San Francisco.

    function ValidateCity(city in varchar2) return varchar2 is
    valid varchar2(50) := 'valid';
begin
    if not REGEXP_LIKE (city, '^[A-Z\s]*$') then
       valid := (case when valid = 'valid' then '' else valid end) 
                 || 'Does not match pattern ^[A-Z\s]*$ ';
    else
       valid := 'Required ';
    end if;
   return valid;
end;

This works when I pass in a single city name like "Tucson" but not when there is a space, e.g. San Francisco or Los Angeles.

Any hints / suggestions / advice is welcome!

Thank you for taking the time for reading this...

Upvotes: 0

Views: 2281

Answers (2)

Jeff Young
Jeff Young

Reputation: 140

For the record, Oracle does support Perl style regular expressions: Perl-Influenced Extensions

Upvotes: 0

fge
fge

Reputation: 121840

normal* (special normal*)* again...

^[[:alpha:]]+([-[:space:]][[:alpha:]]+)*$

Oracle regexes do not support \s, see here. [:alpha:] and [:space:] are POSIX character classes and always need to be in character classes. [-[:space:]] means "a dash, or a space character". You may wish to replace [:space:] with a single space character instead.

[edited to simplify: special here is really only either a dash or a space]

Upvotes: 1

Related Questions