CODEWITHSUNDEEP
mysqlsqlregexflasksqlalchemy
Vamsi Krishna
Vamsi Krishna

Reputation: 495

SQL Alchemy like/regex query for zero or more characters

I have strings in rows which has the following pattern.

1) '"foo": [a, b, c]'
2) '"foo": [aa, c]'
3) '"foo": [aaaa]'
4) '"foo": [b, c, a]'
5) '"foo": [ba, ca, a1234aaa]'

I need to find the Number of rows that have the character a and only and only the character a(not aa or aaaa). [Answer is 2 rows. Row Num 1 and Row Num 4]

Logic: a character preceded by zero or one [space] and ends with zero or one [comma]

I have done:

likestr = '"foo": [%a%]'
query = db.session.query(X).filter(X.Y.like('%'+likestr+'%'))

Obviously, this returns count as 4, while the right answer is 2 How would my query look in regular expressions?

Upvotes: 0

Views: 760

Answers (1)

Linger
Linger

Reputation: 15068

This simple enough to do without regex. The following would work (SQL Fiddle):

SELECT * 
FROM MyTable
WHERE MyField like '%[a,%' 
   OR MyField like '%, a,%' 
   OR MyField like '%, a]%'

Or, if you want to regex (SQL Fiddle):

SELECT * 
FROM MyTable
WHERE MyField REGEXP '\\[a, |, a,|, a\\]';

Upvotes: 1

Related Questions