Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: SQL / First occurence of number in string

    Hi

    I have a string like this:
    "qwertz123"

    How can I get the position of the first occurence of a number in this string, that means the position of the digit 1 ?

    Marc

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I created simple test:
    CREATE TABLE TAB (COL CHAR(9));
    INSERT INTO TAB VALUES ('qwertz123');

    SELECT
    CASE
    WHEN SUBSTR(COL,1,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 1
    WHEN SUBSTR(COL,2,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 2
    WHEN SUBSTR(COL,3,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 3
    WHEN SUBSTR(COL,4,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 4
    WHEN SUBSTR(COL,5,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 5
    WHEN SUBSTR(COL,6,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 6
    WHEN SUBSTR(COL,7,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 7
    WHEN SUBSTR(COL,8,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 8
    WHEN SUBSTR(COL,9,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN 9
    END AS GET_POSITION
    FROM TAB;

    Note: CASE function works as top-down principle. It executes one row from top to down. When the right result is found then function stop execution.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 01-26-04 at 04:54.

  3. #3
    Join Date
    Jul 2003
    Posts
    50
    Thanks, that's exactly what I needed.

    Marc

  4. #4
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by c149187
    Thanks, that's exactly what I needed.

    Marc
    Another option could be to use the LOCATE function.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    GertK
    It will be useful if you can give an example

    sathyaram

    Originally posted by GertK
    Another option could be to use the LOCATE function.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by sathyaram_s
    GertK
    It will be useful if you can give an example

    sathyaram
    Copied from the SQL Reference:


    Example:
    Code:
    Find the location of the letter 'N' (first occurrence) in the word 'DINING'. 
       VALUES LOCATE ('N', 'DINING')
    
    This example returns the following: 
    1
    -----------
              3

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    The following method is a bit more flexible as it is not limited to 9 byte strings...

    WITH NUMERIC_ARRAY (NUM) AS
    (
    VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),(' 8'),('9')
    )
    SELECT MIN(LOCATE(NUM,'ABC123'))
    FROM NUMERIC_ARRAY
    WHERE LOCATE(NUM,'ABC123') != 0
    ;

    You could wrap this up in a function to make life easier...

    CREATE FUNCTION LOCATENUM (STRING VARCHAR(2000))
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    ------------------------------------------------------------------
    -- SQL function to return first index of a numeric in a string
    ------------------------------------------------------------------
    RETURN
    WITH NUMERIC_ARRAY (NUM) AS
    (
    VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),(' 8'),('9')
    )
    SELECT COALESCE(MIN(LOCATE(NUM,STRING)),0)
    FROM NUMERIC_ARRAY
    WHERE LOCATE(NUM,STRING) != 0
    ;

    And then you would simply use...

    VALUES(LOCATENUM('ABC123'))

    You would get a 0 returned when no numeric is found (just like LOCATE).

    HTH

  8. #8
    Join Date
    Jul 2003
    Posts
    50
    Hi

    Is there any performance difference between your solution and grofatys ?

    Marc

  9. #9
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by c149187
    Hi

    Is there any performance difference between your solution and grofatys ?

    Marc
    That's impossible to say. For a short string, I would guess that Grofaty's method is slightly more efficient. As the string gets longer, I reckon the method I have shown would prove more efficient, plus the coding for the other method would become impossible for really long strings.

    I'd suggest that you suck it and see. I don't think either method is a killer!
    Last edited by Damian Ibbotson; 01-27-04 at 06:42.

  10. #10
    Join Date
    Jul 2003
    Posts
    50
    Hi

    - Explain shows
    Damian: 7200 Costs
    Grofaty: 6000 Costs

    - Read 500 records
    Damian: 0.15s
    Grofaty: 0.15s

    - Read 10'000 records
    Damian: 4s
    Grofaty: 2s

    Marc

  11. #11
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by c149187
    Hi

    - Explain shows
    Damian: 7200 Costs
    Grofaty: 6000 Costs

    - Read 500 records
    Damian: 0.15s
    Grofaty: 0.15s

    - Read 10'000 records
    Damian: 4s
    Grofaty: 2s
    That would make sense. The method I have shown would have to perform 10 'LOCATES' on every row (One for each numeric char 0-9). Grofaty's method would perform an average of around 5 checks on a 'SUBSTR' on each row (assuming that there is an equal probability of the first numeric being in any of the positions in your input string).

    If your input string were say 40 bytes, the LOCATE method would still only have to perfom 10 LOCATES, whereas the CASE method could perform anywhere between 1 and 40 checks on each row depending on the dispersion of the numeric characters.

    Not very scientific I know but it gives you an idea.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •