1

I would like to query a database which is MySQL 5.

Let's say database name is db and the table name is table and the column name is column

and that column is a text

containing the following for example:

aksksksksjsjk&ct=100&lor=10
aksksksksjsjk&ct=1001001001001001&lor=10

So i would like to query that table and grep only where ct start with number 1 and it's 16 numbers.

I tried with SELECT column FROM db.table WHERE column LIKE '%ct=1%'

so it's gonna grep where ct start with number 1

so kindly try to help me to proceed with select ct when start with number 1 and contain 16 numbers

Doumer Issac
  • 13
  • 2

1 Answers1

1

In its basic form, you might want to use

SELECT column FROM db.table WHERE column REGEXP BINARY 'ct=1[0-9]{15}'

Or, to match as a whole word:

SELECT column FROM db.table WHERE column REGEXP BINARY '[[:<:]]ct=1[0-9]{15}[[:>:]]'

Note that [0-9]{15} matches 15 digits.

The BINARY keyword will make matching case sensitive, so only ct will get matched and CT won't. Remove it if you need to keep the regex case insensitive.

The [[:<:]] matches the left-hand (starting) word boundary and [[:>:]] matches the trailing (end) word boundary.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • Thank you so much. it's work. mind if i asked you another question? in case if the same text include `exp=0918` so are there's way to select `exp` bigger than `0918` ? – Doumer Issac Nov 08 '18 at 09:38
  • @DoumerIssac That is a [complex topic](https://stackoverflow.com/questions/22130429/using-regular-expressions-to-validate-a-numeric-range). However, using [the number range generator](http://gamon.webfactional.com/regexnumericrangegenerator/), you may try `0*(919|9[2-9][0-9]|[1-9][0-9]{3,})` – Wiktor Stribiżew Nov 08 '18 at 09:43