# Thread: SQL / First occurence of number in string

1. Registered User
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. Registered User
Join Date
Jan 2003
Posts
1,626
Provided Answers: 1
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 03:54.

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

Marc

4. Registered User
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. Super Moderator
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.

6. Registered User
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. Padawan
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. Registered User
Join Date
Jul 2003
Posts
50
Hi

Is there any performance difference between your solution and grofatys ?

Marc

9. Padawan
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 05:42.

10. Registered User
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. Padawan
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
•