MySQL select from text column with specification

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

1 answer

  • answered 2018-11-08 08:34 Wiktor Stribi┼╝ew

    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.