MySQL Find location of first non-numeric character in string

Question

I would like to find the location of the first non-numeric (0-9) character in a string. That is, a space, a dash, a letter or anything but a number.

I seem unable to combine locate with regex to get a result like this:

Data      Output
----------------
12a       3
5E914     2
345-347   4
4 c       2

@RiggsFolly The question solution which you refer to is not applicable to MySQL. Check DBMS before closing. — Akina, Sep 30 '20 at 09:38
Why would it not be applicable? The answer that you refer you uses PATINDEX, which I cannot use in MySQL. — Braamstruik, Sep 30 '20 at 10:04

score 0 · Answer 1 · answered Sep 30 '20 at 09:37

0

SELECT REGEXP_INSTR(`Input`, '[^0-9]') AS `Output`
FROM `table`

answered Sep 30 '20 at 09:37

Akina

When I try to use REGEXP_INSTR, I get the error "SQL Error [1046] [3D000]: No database selected". I also think that your code returns the location of the first numeric value, not the first non-numeric. – Braamstruik Sep 30 '20 at 10:06
@Braamstruik *When I try to use REGEXP_INSTR, I get the error "SQL Error [1046] [3D000]: No database selected".* That is your client settings problem, not query problem. Edit my.ini and add proper default database, or execute `USE databasename` before. *I also think that your code returns the location of the first numeric value, not the first non-numeric.* Do not think - test simply. – Akina Sep 30 '20 at 10:10

1 Answers1