Testing String for number 1

[IanWaterman]

I need to move a formula from Crystal Reports into an Oracle view.

In the UK post codes can be

AB99 8AA ie alpha alpha number plus space and second set
or
A9 8AA ie alpha number

The last set is always 3 characters and preceded by a space. The first set can be 2, 3 or 4 characters in length.

I need to extract first 1 or two alphas, next one or two digits and the final set.

Final set is no problem, for the first part I did this is Crystal by checking whether character 2 was a number

If isnumeric(mid({RPT_BFS_PROPERTY_SI.RISKADDRESS5}, 2, 1)) = true then Left({RPT_BFS_PROPERTY_SI.RISKADDRESS5},1)
ELSE
Left({RPT_BFS_PROPERTY_SI.RISKADDRESS5},2)

I can replace the left and mid functions with substr, but
There does not appear to be an isnumeric function within Oracle, can any one suggest how I can overcome this?

Thank you

Ian

 

[oradba101]

Greetings,

You could try regular expressions:

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

Regards,

William Chadbourne
Oracle DBA

 

[cmmrfrds]

The regexp works great for pattern matching of which numeric is one of the patterns. I like that it returns what you ask for in the pattern and eliminates extra coding.

Here are some examples.

SELECT
REGEXP_INSTR('abc123', '[^0-9]+') as ins1,
REGEXP_INSTR('abc123', '[0-9]+') as ins2,
REGEXP_INSTR('abc123', '[[:digit:]]+') as ins3,
-- what is returned is what you ask for in the pattern.
REGEXP_SUBSTR('abc123', '[^0-9]+') as subs1,
REGEXP_SUBSTR('abc123', '[0-9]+') as subs2,
REGEXP_REPLACE('abc123xyz', '[^0-9]+') as repl1,
REGEXP_REPLACE('abc123xyz', '[0-9]+') as repl2
FROM dual WHERE REGEXP_LIKE('a123', '[^0-9]+');

 

[jimirvine]

Nice example. You could also have included the POSIX charset:

SELECT
    REGEXP_INSTR('abc123', '[^[:digit:]]+') as ins1,
    REGEXP_INSTR('abc123', '[[:digit:]]+') as ins2,
    REGEXP_INSTR('abc123', '[[:digit:]]+') as ins3,    
    -- what is returned is what you ask for in the pattern.    
    REGEXP_SUBSTR('abc123', '[^[:digit:]]+') as subs1,
    REGEXP_SUBSTR('abc123', '[[:digit:]]+') as subs2,
    REGEXP_REPLACE('abc123xyz', '[^[:digit:]]+') as repl1,      
    REGEXP_REPLACE('abc123xyz', '[[:digit:]]+') as repl2            
FROM dual WHERE REGEXP_LIKE('a123', '[^[:digit:]]+');

 

[ChrisHunt]

In the UK post codes can be

AB99 8AA ie alpha alpha number plus space and second set
or
A9 8AA ie alpha number

Actually that's not entirely true. The outcode (that's the first bit) can also finish with a letter - for example the House of Commons is at SW1A 1AA. There's also the weird postcode allocated to Girobank of "GIR 0AA". There's a good description of the whole postcode maze at

http://en.wikipedia.org/wiki/UK_postcodes

.

Regular expressions are still the best way to deal with this problem, but they were introduced in 10g and this is a 9i forum!

So, here's another approach. I'm going to ignore the SW1A style postcodes on the assumption that if you didn't know about them you probably don't need to deal with them! Here's my method...

SELECT TRANSLATE(SUBSTR(postcode,1,2),'A1234567890','A')letters,
       TRANSLATE(SUBSTR(postcode,1,INSTR(postcode,' ')),'1ABCDEFGHIJKLMNOPQRSTUVWXYZ ','1') numbers
FROM   your_table

Having said that, a more robust approach would be to write some PL/SQL functions (or, better still, a package) that picks out the bits that you want in a less obscure way.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ChrisHunt]

Here's a deluxe solution - a package for pulling bits out of postcodes:

CREATE OR REPLACE PACKAGE postcode AS
   FUNCTION tidy(p_postcode IN VARCHAR2) RETURN VARCHAR2;

   PROCEDURE split (p_postcode IN VARCHAR2,
                    p_area     OUT VARCHAR2,
                    p_district OUT VARCHAR2,
                    p_sector   OUT VARCHAR2,
                    p_unit     OUT VARCHAR2);
                    
   PROCEDURE split (p_postcode IN VARCHAR2,
                    p_outcode  OUT VARCHAR2,
                    p_incode   OUT VARCHAR2);

   FUNCTION area(p_postcode IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION district(p_postcode IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION sector(p_postcode IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION unit(p_postcode IN VARCHAR2) RETURN VARCHAR2;

   FUNCTION outcode(p_postcode IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION incode(p_postcode IN VARCHAR2) RETURN VARCHAR2;
END postcode;
/

CREATE OR REPLACE PACKAGE BODY postcode AS
   g_postcode VARCHAR2(32767);
   
   g_area     VARCHAR2(8);
   g_district VARCHAR2(8);
   g_sector   VARCHAR2(8);
   g_unit     VARCHAR2(8);

   FUNCTION tidy(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_squash  VARCHAR2(7);
      v_pattern VARCHAR2(7);
      v_tidy    VARCHAR2(8);
   BEGIN
      v_squash := SUBSTR(REPLACE(UPPER(p_postcode),' ',''),1,7);
      
      v_pattern := TRANSLATE(v_squash,
                             'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',
                             'XXXXXXXXXXXXXXXXXXXXXXXXXX9999999999');

      IF v_pattern = 'X99XX' THEN
         v_tidy := SUBSTR(v_squash,1,2)||' '||SUBSTR(v_squash,3);
      ELSIF v_pattern IN ('XX99XX','X999XX','X9X9XX') THEN
         v_tidy := SUBSTR(v_squash,1,3)||' '||SUBSTR(v_squash,4);
      ELSIF v_pattern IN ('XX999XX','XX9X9XX') THEN
         v_tidy := SUBSTR(v_squash,1,4)||' '||SUBSTR(v_squash,5);
      ELSIF v_squash= 'GIR0AA' THEN
         v_tidy := 'GIR 0AA';
      ELSE
         v_tidy := SUBSTR(UPPER(p_postcode),1,8); -- Could return NULL here
      END IF;

      RETURN v_tidy;
   END;

/* ------------------------------------------------------------------- */

   PROCEDURE split (p_postcode IN VARCHAR2,
                    p_area     OUT VARCHAR2,
                    p_district OUT VARCHAR2,
                    p_sector   OUT VARCHAR2,
                    p_unit     OUT VARCHAR2) IS
      v_tidy  VARCHAR2(8);
      v_pos   NUMBER := 1;
      v_len   NUMBER;
      v_char  VARCHAR2(1);
   BEGIN
      IF p_postcode IS NULL THEN
         NULL;
      ELSIF p_postcode = g_postcode THEN
         p_area     := g_area;
         p_district := g_district;
         p_sector   := g_sector;
         p_unit     := g_unit;
      ELSE
         v_tidy := tidy(p_postcode);
         v_len  := LENGTH(v_tidy);
   
         LOOP
            EXIT WHEN v_pos > v_len;
            v_char := SUBSTR(v_tidy,v_pos,1);
   
            IF v_char IN ('1','2','3','4','5','6','7','8','9','0',' ') THEN
               EXIT;
            ELSE
               p_area := p_area||v_char;
            END IF;
            
            v_pos := v_pos + 1;
         END LOOP;
    
         LOOP
            EXIT WHEN v_pos > v_len;
            v_char := SUBSTR(v_tidy,v_pos,1);
            IF v_char = ' ' THEN
               EXIT;
            ELSE
               p_district := p_district||v_char;
            END IF;
            
            v_pos := v_pos + 1;
         END LOOP;
   
         p_sector := SUBSTR(v_tidy,v_pos+1,1);
         p_unit := SUBSTR(v_tidy,v_pos+2,2);
    
         g_postcode := p_postcode;
         g_area     := p_area;
         g_district := p_district;
         g_sector   := p_sector;
         g_unit     := p_unit;
      END IF;
   END;
                    
   PROCEDURE split (p_postcode IN VARCHAR2,
                    p_outcode  OUT VARCHAR2,
                    p_incode   OUT VARCHAR2) IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);

      p_outcode := v_area||v_district;
      p_incode  := v_sector||v_unit;
   END;

/* ------------------------------------------------------------------- */

   FUNCTION area(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);
      
      RETURN v_area;
   END;

/* ------------------------------------------------------------------- */
      
   FUNCTION district(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);
      
      RETURN v_district;
   END;

/* ------------------------------------------------------------------- */

   FUNCTION sector(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);
      
      RETURN v_sector;
   END;

/* ------------------------------------------------------------------- */

   FUNCTION unit(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);
      
      RETURN v_unit;
   END;

/* ------------------------------------------------------------------- */

   FUNCTION outcode(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);
      
      RETURN v_area||v_district;
   END;

/* ------------------------------------------------------------------- */

   FUNCTION incode(p_postcode IN VARCHAR2) RETURN VARCHAR2 IS
      v_area     VARCHAR2(8);
      v_district VARCHAR2(8);
      v_sector   VARCHAR2(8);
      v_unit     VARCHAR2(8);
   BEGIN
      split(p_postcode,v_area,v_district,v_sector,v_unit);
      
      RETURN v_sector||v_unit;
   END;
END postcode;
/

Most of the work takes place in the [tt]split()[/tt] procedure (which could probably be more elegantly coded, but I was in a hurry!). The other functions call this procedure and return the bit(s) you want.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[HarleyQuinn]

One note on Regular Expressions, they were introduced in 10g and we're in the 9i forum, so most likely not a viable solution for then OP.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Ascalonian]

BEGIN
vn_number NUMBER;

vn_number := TO_NUMBER(vs_string_to_check);

EXCEPTION
    WHEN OTHERS THEN
       vn_number := -1;
END;

Yes, I know this is horrible. But you could call TO_NUMBER and if you get an exception, then it was not a number. Harsh, but works.

 

[IanWaterman]

Thanks every one for their input, I have gone with Chris's first suggestion as it seems to be the simplest and I understand it. Being a simple soul.

Ian

 

[Welshbird]

I'm not sure I really understand what you need to get - but if you want sectors and half-sectors then you can find those much more easily:

Just calculate the length of the field, and then trim off the last 1 or 2 characters.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]