Mysql RegExp select all string with matching sequence if the string is 5 character long

Asked Nov 24 '16 at 15:13

Active Nov 24 '16 at 15:14

Viewed 22 times

In MySQL if a string in a column is 5 or less characters long or then select them else discard them. I may be not good in explaining them but the following scenario will explain it. I have data in column

Ram.
RamS.
Ram-S
Ram..
RAm.
RAmS.
Ramazan..
9R1..
9R11.
9R12.
9R13.
9R14.
9R15.
9R1Z.
9R1rA
9R1-S
9R1rAMS
9R1rR
9R1S-
9R1aA
9R1rAMS123

If it is 5 character long and if there is a dot then select all the corresponding values like this for value 9R1

9R1..
9R11.
9R12.
9R13.
9R14.
9R15.
9R1Z.
9R1rA
9R1-S
9R1rR
9R1S-
9R1aA

I tried this

SELECT * FROM `ramstest`
WHERE  `Test1`
REGEXP BINARY "^9R1[A-Za-z\\.\\-]{1,2}"

but did not work. I will appreciate some help

edited Nov 24 '16 at 15:14

Jens

67,715
15
98
113

asked Nov 24 '16 at 15:13

Ramazan Khan

What means `but did not work`? – Jens Nov 24 '16 at 15:16
that query returns 9 row plus it has 9R1rAMS123 kind of info as well – Ramazan Khan Nov 24 '16 at 15:17
1

Use $ at the end of the regex `REGEXP BINARY "^9R1[A-Za-z\\.\\-]{1,2}$"` – Jens Nov 24 '16 at 15:19
You want to the distinct first 5 characters matching `9R1...`? – R. Chappell Nov 24 '16 at 15:23
Yes @ R. Chappell – Ramazan Khan Nov 24 '16 at 15:53
Thanks Jens it worked just modified the query a little bit it worked like magic thanks again. – Ramazan Khan Nov 24 '16 at 15:56
Just for the record the query is – Ramazan Khan Nov 24 '16 at 15:56
SELECT * FROM `ramstest` WHERE `Test1` REGEXP BINARY "^9R1[A-Za-z0-9\\.\\-]{1,2}$" – Ramazan Khan Nov 24 '16 at 15:56

Mysql RegExp select all string with matching sequence if the string is 5 character long

0 Answers0