Xtykutl

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&rxp=0918&rpa=100

aksksksksjsjk&ct=100&rxp=1018&ls=1

So i would like to query that table and grep only where rxp > 0918

so I'm expecting the result to be:

aksksksksjsjk&ct=100&rxp=1018&ls=1

I tried with SELECT column FROM db.table WHERE column LIKE '%rxp=1018%'and it's working ! but that's meant that i will need to manually insert all dates !

Assuming that the value that comes after rxp= is always of length 4 in the format MMYY then the following should do the trick for you. Note that since your date is in string format, we need to perform check for both the month and year separately.

SELECT *

FROM db.table

WHERE LEFT(SUBSTRING_INDEX(testColumn, 'rxp=', -1), 2)  > '09' 

    AND MID(SUBSTRING_INDEX(testColumn, 'rxp=', -1), 3, 2)  >= '18'

Assuming you've got the following values under column:

aksksksksjsjk&ct=100&rxp=0918&rpa=100

aksksksksjsjk&ct=100&rxp=1018&ls=1

aksksksksjsjk&ct=100&rxp=1116&ls=1

The output of the query should be

aksksksksjsjk&ct=100&rxp=1018&ls=1

However, I would highly recommend you to normalise your table and store every value in a separate column (ct, rxp, rpa, ls etc.) instead of having a lot of information combined into a single string.

create table #temp(

Integ Integer

 )

insert into #temp values(CONVERT(INT, right(left('aksksksksjsjk&ct=100&rxp=0918&rpa=100', 29),4)) ) ,

                        (CONVERT(INT, right(left('aksksksksjsjk&ct=100&rxp=1018&ls=1', 29),4)) ) 

select * from #temp

where Integ >918

drop table #temp

hope it helps you