0

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
jarlh
  • 42,561
  • 8
  • 45
  • 63
user3225385
  • 1
  • 2
  • 2
    Don't store data as comma separated items, it will only cause you lots of trouble. – jarlh Feb 11 '19 at 10:39
  • This is already present i need to find out only rows with values 30 plus. – user3225385 Feb 11 '19 at 10:40
  • 1
    you have an issue finding a query because you are storing all the values for an application in a single comma separated field. Change your table to one row for each value of each application. you will have multiple rows for each application and it will be trivial to query as you want – Lelio Faieta Feb 11 '19 at 10:41

1 Answers1

0

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
)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks Tim.It Worked.If you could add a little explanation of what you did above that would be very helpful in future. – user3225385 Feb 11 '19 at 10:50
  • @user3225385 I have added a brief explanation of the regex. A full explanation of how regex works would be out of scope for your question. – Tim Biegeleisen Feb 11 '19 at 11:02