MySQL Find location of first non-numeric character in string [duplicate]

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
– Akina, Commented Sep 30, 2020 at 9:38
Why would it not be applicable? The answer that you refer you uses PATINDEX, which I cannot use in MySQL. — Braamstruik
– Braamstruik, Commented Sep 30, 2020 at 10:04

Akina · Accepted Answer · 2020-09-30 09:37:29Z

0

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

answered Sep 30, 2020 at 9:37

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Braamstruik Over a year ago

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.

Akina Over a year ago

@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.

Collectives™ on Stack Overflow

MySQL Find location of first non-numeric character in string [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related