Find if value greater than a number exists in comma separated string in MySql Column [duplicate]

37
February 12, 2019, at 05:40 AM

This question already has an answer here:

  • Is storing a delimited list in a database column really that bad? 10 answers

i have a table with values given below

payment_pattern application
0,0,117,9,5  XXXX0004DqjBQAS
0,30,0,29,16,0   XXX000004E79tQAC
30,30,23,29,22,1 XXX000006F2brQAC
0,0,0,0,29,28    XXXB000006Fs3oQAC 

Need to find regular expression that would return all rows with values greater than or equal to 30.

Example output should be

0,0,117,9,5 XXXX0004DqjBQAS
0,30,0,29,16,0  XXXX0004E79tQAC
30,30,23,29,22,1 XXXX0006F2brQAC 
Answer 1

You could use REGEXP here:

SELECT payment_pattern, application
FROM yourTable
WHERE payment_pattern REGEXP '[[:<:]]([3-9][0-9]|[1-9][0-9][0-9][0-9]*)[[:>:]]';

But, you would do better to not store unnormalized CSV data in your tables. Your query would be trivial if each number were stored in a separate record.

Here is a demo for the above regex:

Demo

Here is a brief explanation of the above regex:

(
[3-9][0-9]             match 30 to 99
[1-9][0-9][0-9][0-9]*  match 100 to 999, or this plus any other digit
)
READ ALSO
PDF jpeg image compression for DCTDecode

PDF jpeg image compression for DCTDecode

I'm using android pdf writer apw-library to generate a pdf document

59
How can I attach multiple extras to a single Intent in the Bundle object?

How can I attach multiple extras to a single Intent in the Bundle object?

I'm receiving several notifications each with its own unique id

40
React Native TextField component in external library on focus

React Native TextField component in external library on focus

I have a Register Component that has PhoneInput Component from the external library https://githubcom/thegamenicorus/react-native-phone-input The problem is I want to execute some code as soon as TextField inside the PhoneInput is Focused

69
Installing Flutter on Mac OS

Installing Flutter on Mac OS

on running flutter doctor i get the below dependency lacking :

65