MySQL select from text column with filtering

Question

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 !

Giorgos Myrianthous · Accepted Answer · 2018-11-08 10:26:06Z

2

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.

edited Nov 8, 2018 at 10:26

answered Nov 8, 2018 at 10:14

Giorgos Myrianthous

40.4k21 gold badges156 silver badges175 bronze badges

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

1 Comment

Ike White Over a year ago

you mentioned FROM db.table A so what is A refer to ?

Vaso Miruashvili · Accepted Answer · 2018-11-08 10:40:53Z

0

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

answered Nov 8, 2018 at 10:40

Vaso Miruashvili

1091 silver badge13 bronze badges

Collectives™ on Stack Overflow

MySQL select from text column with filtering

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related