Checking field format. 1

[DerickD]

Hi All,

I have started working with Oracle and I have some very basic questions I am sure.
Anyway,

I want to check the a field contains 4 letters then 2 numbers :

1234ab = true
1234 ab = true
ab1234 = false
1a2b34 = false
12a = false
1a234 = false
1234abc = false
abc12345 = false
1234abcdefg56789 = false
...etc...

Can you help me with this?

Thanks allot,
DerickD

 

[carp]

I think I would approach this with a function; something like the following should do it:

CREATE OR REPLACE FUNCTION fournum_twoltr(p_string IN VARCHAR2) RETURN NUMBER AS
-- PURPOSE: DETERMINE IF A STRING CONTAINS FOUR NUMBERS AND TWO LETTERS
--
   l_flag NUMBER := 1; -- 0=INVALID, 1=VALID
   l_test NUMBER;
--
BEGIN
   IF (LENGTH(p_string) != 6) THEN
      RETURN 0;
   ELSE
      BEGIN
         l_test := TO_NUMBER(SUBSTR(p_string, 1,4));
         IF (SUBSTR(p_string,5,2) BETWEEN 'AA' AND 'zz') THEN
            RETURN 1;
         ELSE 
            RETURN 0;
         END IF;
      EXCEPTION
         WHEN INVALID_NUMBER OR VALUE_ERROR THEN
            RETURN 0;
      END;
   END IF;
END;
/

To use this, you would pass your string into the function and observe the returned results:

08:59:42 SQL> select fournum_twoltr('123dr4') from dual;

FOURNUM_TWOLTR('123DR4')
------------------------
                       0

08:59:51 SQL> select fournum_twoltr('123456') from dual;

FOURNUM_TWOLTR('123456')
------------------------
                       0

09:00:00 SQL> select fournum_twoltr('123dr4') from dual;

FOURNUM_TWOLTR('123DR4')
------------------------
                       0

09:01:59 SQL> select fournum_twoltr('123456') from dual;

FOURNUM_TWOLTR('123456')
------------------------
                       0

09:02:03 SQL> select fournum_twoltr('1234dr') from dual;

FOURNUM_TWOLTR('1234DR')
------------------------
                       1

09:02:10 SQL> select fournum_twoltr('1234Dr') from dual;

FOURNUM_TWOLTR('1234DR')
------------------------
                       1

09:02:16 SQL> select fournum_twoltr('1234DQ') from dual;

FOURNUM_TWOLTR('1234DQ')
------------------------
                       1

Elbert, CO
0902 MDT

 

[carp]

Just realized I left some dead code in! Eliminate the declaration of l_flag; it's an artifact.

 

[sem]

SUBSTR(p_string, 1,4) should contain digits only (not fractional part delimiter, sign or other characters valid for numbers), thus my variant (or to be more precise Carp's one edited by me) is:

CREATE OR REPLACE FUNCTION fournum_twoltr(p_string IN VARCHAR2) RETURN NUMBER AS
-- PURPOSE: DETERMINE IF A STRING CONTAINS FOUR NUMBERS AND TWO LETTERS
l_tail varchar2(2); -- 2 letters

BEGIN
l_tail := rtrim(ltrim(p_string,'0123456789'));

IF l_tail BETWEEN 'AA' AND 'zz' then
return 1;
END IF;

RETURN 0;

exception
when value_error then return 0;
END;

Obviously this works only for Latin letters

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Thanks, Dima - have a star! I had a feeling there was a cleaner way to do this, but had to go back to work!

However, I believe your function would allow invalid formats in. A passed-in string could have more than six characters (e.g., '12345678ab') or insufficient characters (e.g., '1234a'). So I think we need to converge on something like:

CREATE OR REPLACE FUNCTION fournum_twoltr(p_string IN VARCHAR2) RETURN NUMBER AS
-- PURPOSE: DETERMINE IF A STRING CONTAINS FOUR NUMBERS AND TWO LETTERS
   l_tail varchar2(2); -- 2 letters
BEGIN
      l_tail := rtrim(ltrim(p_string,'0123456789'));
      IF (LENGTH(p_string)=6 AND 
          LENGTH(l_tail) = 2 AND
          l_tail BETWEEN 'AA' AND 'zz') THEN
         return 1;
      END IF;
      RETURN 0;      
exception
   when value_error then return 0;
END;
/

Elbert, CO
1059 MST

 

[sem]

I knowingly did not check length because of the second example in original request:

1234 ab = true

Though the lenth of l_tail must be verified indeed. I found one more problem. BETWEEN 'AA' AND 'zz' allows 'B1', so it should be changed. Unfortunately I have no time to invent something better :-(

CREATE OR REPLACE FUNCTION fournum_twoltr(p_string IN VARCHAR2) RETURN NUMBER AS
-- PURPOSE: DETERMINE IF A STRING CONTAINS FOUR NUMBERS AND TWO LETTERS
   l_tail varchar(2); -- 2 letters

BEGIN
      l_tail := rtrim(ltrim(p_string,'0123456789'));
      
      
      IF (length(l_tail)=2 
      and upper(substr(l_tail,1,1))!=lower(substr(l_tail,1,1))
      and upper(substr(l_tail,1,2))!=lower(substr(l_tail,1,2))) 
      then
         raise VALUE_ERROR;
      END IF;

      RETURN 0;      

exception
   when VALUE_ERROR then return 0;
END;

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Dima -

Again, your eyes are better than mine!

Elbert, CO
0823 MDT