Search If Number Is Contained Within an Expression Like: 1-3,5,10-15,20

Search if number is contained within an expression like: 1-3,5,10-15,20

It is possible to do this all in SQL by use of the REGEXP_SUBSTR function and hierarchical queries:

with list_of_ids as (
select regexp_substr(a, '[[:digit:]]+',1, 1) as lot1
     , nvl( regexp_substr(a, '(-)([[:digit:]]+)',1, 1, 'i', '2')
          , regexp_substr(a, '[[:digit:]]+',1, 1)) as lot2
  from (select regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) as a
          from dual
       connect by regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) is not null
               )
       )
select a.*
  from products a
  join list_of_ids b
    on a.lot between b.lot1 and b.lot2

However, I must emphasise that normalising your database properly is the way to go. This solution may not scale well and does a hugely unnecessary amount of work.

It works like this:

First split your data on the comma:

SQL>  select regexp_substr('1-3,5,10-15,20', '[^,]+', 1, level) as a
  2     from dual
  3  connect by regexp_substr('1-3,5,10-15,20', '[^,]+', 1, level) is not null
  4          ;

A
--------------
1-3
5
10-15
20

Next, split it on the hyphen to provide a minimum and maximum lot to use in the BETWEEN before finally joining it to the table. The NVL is there to ensure that there is always a maximum.

SQL> select regexp_substr(a, '[[:digit:]]+',1, 1) as lot1
  2       , nvl( regexp_substr(a, '(-)([[:digit:]]+)',1, 1, 'i', '2')
  3             , regexp_substr(a, '[[:digit:]]+',1, 1)) as lot2
  4    from (select regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) as a
  5            from dual
  6         connect by regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) is not null
  7                 )
  8         ;

LOT1           LOT2
-------------- --------------
1              3
5              5
10             15
20             20

SQL>

Here's a working SQL Fiddle with the full query.

Regular expression to match standard 10 digit phone number

^(\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$

Matches the following

123-456-7890
(123) 456-7890
123 456 7890
123.456.7890
+91 (123) 456-7890

If you do not want a match on non-US numbers use

^(\+0?1\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$

Update :
As noticed by user Simon Weaver below, if you are also interested in matching on unformatted numbers just make the separator character class optional as [\s.-]?

^(\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$

How to calculate some points given a range of numbers

How about Sequence of Triangular Number ?

Equation : x_n = n(n+1)/2

Sequence (start at n = 0) : 0, 1, 3, 6, 10, 15, 21, 28, 36, 45, 55, 66, 78, 91, 105, 120, 136, 153, 171, 190, 210, 231, 253, 276, 300, 325, 351, 378, 406, 435, 465, 496, 528, 561, ...

Input : 1 to 100

Min value = 1 , Max value = 100

1 + 0 = 1

1 + 1 = 2

2 + 3 = 5

5 + 6 = 11

11 + 10 = 21

21 + 15 = 36

36 + 21 = 57

and so on.. until the evaluated number exceeds max value

Sequence : 1, 2, 5, 11, 21, 36, 57 and so on ...

Input : 1000 to 10000

Min value = 1000 , Max value = 10000

1000 + 0 = 1000

1000 + 1 = 1001

1001 + 3 = 1004

1004 + 6 = 1010

1010 + 10 = 1020

1020 + 15 = 1035

1035 + 21 = 1056

and so on.. until the evaluated number exceeds max value

Sequence : 1000, 1001, 1004, 1010, 1020, 1035, 1056 and so on ...

I will leave the implementation part for you to try it out.

how to check if list of values are in range of values present in two columns?

try this :

import pandas as pd
df = pd.DataFrame({'columnA': [1,6,11,16], 'columnB': [5,10,15,20]})
lst = [1,2,3,6,7,12,19]
df['Counts']=0
for index,row in df.iterrows():
    column_a=df.loc[index,'columnA']
    column_b=df.loc[index,'columnB']
    counts=[]
    for value in lst:
        if (value >= column_a) & (value  <= column_b):
            df.loc[index,'Counts']+=1

Output :

How do I multiply each element in a list by a number?

You can just use a list comprehension:

my_list = [1, 2, 3, 4, 5]
my_new_list = [i * 5 for i in my_list]

>>> print(my_new_list)
[5, 10, 15, 20, 25]

Note that a list comprehension is generally a more efficient way to do a for loop:

my_new_list = []
for i in my_list:
    my_new_list.append(i * 5)

>>> print(my_new_list)
[5, 10, 15, 20, 25]

As an alternative, here is a solution using the popular Pandas package:

import pandas as pd

s = pd.Series(my_list)

>>> s * 5
0     5
1    10
2    15
3    20
4    25
dtype: int64

Or, if you just want the list:

>>> (s * 5).tolist()
[5, 10, 15, 20, 25]

Finally, one could use map, although this is generally frowned upon.

my_new_list = map(lambda x: x * 5, my_list)

Using map, however, is generally less efficient. Per a comment from ShadowRanger on a deleted answer to this question:

The reason "no one" uses it is that, in general, it's a performance
pessimization. The only time it's worth considering map in CPython is
if you're using a built-in function implemented in C as the mapping
function; otherwise, map is going to run equal to or slower than the
more Pythonic listcomp or genexpr (which are also more explicit about
whether they're lazy generators or eager list creators; on Py3, your
code wouldn't work without wrapping the map call in list). If you're
using map with a lambda function, stop, you're doing it wrong.

And another one of his comments posted to this reply:

Please don't teach people to use map with lambda; the instant you
need a lambda, you'd have been better off with a list comprehension
or generator expression. If you're clever, you can make map work
without lambdas a lot, e.g. in this case, map((5).__mul__, my_list), although in this particular case, thanks to some
optimizations in the byte code interpreter for simple int math, [x * 5 for x in my_list] is faster, as well as being more Pythonic and simpler.

Swap two digits if their range is descending

You can use the Regex.Replace method with a MatchEvaluator instance as third parameter to call a function that returns the replacement string. Example:

using System;
using System.Text.RegularExpressions;

public class Example
{
   public static void Main()
   {
      string sContent = "1,3,5,14-10,15-20";       

      MatchEvaluator evaluator = new MatchEvaluator(LowerThan);

      Console.WriteLine(Regex.Replace(sContent, @"(\d+)-(\d+)", evaluator));
   }

   public static string LowerThan(Match match)
   {
      if (int.Parse(match.Groups[1].Value) > int.Parse(match.Groups[2].Value)) {
         return match.Groups[2].Value + "-" + match.Groups[1].Value;
      }
      return match.Value;
   }
}

Can (a== 1 && a ==2 && a==3) ever evaluate to true?

If you take advantage of how == works, you could simply create an object with a custom toString (or valueOf) function that changes what it returns each time it is used such that it satisfies all three conditions.

const a = {  i: 1,  toString: function () {    return a.i++;  }}
if(a == 1 && a == 2 && a == 3) {  console.log('Hello World!');}

---

Related Topics