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

Improve this question

1 Answer 1

Reset to default
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.

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

2 Comments

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 ?
@DoumerIssac That is a complex topic. However, using the number range generator, you may try 0*(919|9[2-9][0-9]|[1-9][0-9]{3,})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.