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

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

Thanks, that's exactly what I needed.

Marc

Another option could be to use the LOCATE function.

GertK
It will be useful if you can give an example

sathyaram

Copied from the SQL Reference:


Example:

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

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!

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

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.