SQL: Test if Number Contains Digits of Another Number

Question

I'm interested in using SQL to test if one number, stored in column DOW, contains the digits of another number, also stored in DOW, regardless of separation by other digits. Here are the current numbers with which I'm working, although I may have more to deal with in the future:

23
236
1234
12346
123456
67

If the query checks 123456 against 236, it needs to return true. Vice versa, 236 against 123456 returns false. Another example is 1234 returns true when checked against 23, but 67 returns false when checked against 12346. If I've not provided enough information in this question, please ask for clarification.

Simplified Version of my Query:

SELECT t1.DOW, t2.DOW  
FROM table t1, table t2  
WHERE /* t2.DOW contains all digits regardless of separation in t1.DOW */

Thanks!

SQL is especially poor at emulating an array. You would best use another tool. — dkretz, Apr 30 '11 at 22:59
This part doesn't make sense to me: "If the query checks 123456 against 236, it needs to return true. Vice versa, 236 against 123456 returns false." All four numbers contain the digits 2, 3, and 6. Typo, perhaps? — Mike Sherrill 'Cat Recall', Apr 30 '11 at 23:26
@Catcall - 123456 contains all the digits of 236 but not vice versa. — Martin Smith, May 01 '11 at 00:32
Comparing 233 to 1234, should that return true or false? All the digits of 233 are contained in 1234, but there are more 3s in the first one. — Mike Sherrill 'Cat Recall', May 01 '11 at 00:42
@ponies - the difficulty with which the question is answered doesn't support your contention. I don't see your answer yet. I can think of several ways to do this using languages supporting real arrays. — dkretz, May 01 '11 at 01:22

score 0 · Answer 1 · answered Apr 30 '11 at 23:19

This can be handled as a stored procedure which turns a value into a string and inspects the string for each of the digits in the pattern value.

The guts of the stored procedure would have something like

   function has_digits (pattern, value)
   string s = string (value)
   count = 0
   for each char in pattern
      if instr (s, char) > 0  // for mysql
         count = count + 1
   return count == pattern.length()

(The specifics of this greatly depend on which SQL flavor is used.) This would be used like

SELECT *
 FROM sometable
 WHERE has_digits (236, somefield);

ypercubeᵀᴹ · Answer 2 · 2011-05-01T09:35:28.343

For MySQL only:

CREATE TABLE num          --- a help table
( i INT PRIMARY KEY
) ;

INSERT INTO num
VALUES
  (1), (2), (3), (4)
, (5), (6), (7), (8)
, (9),(10),(11),(12) 
,(13),(14),(15),(16)
,(17),(18),(19),(20) ;    --- maximum number of digits = 20

Then, you can use this (horrible) query, which first converts numbers to strings and add % between each digit (23 would be converted to '%2%3%') and then tests WHERE 23456 LIKE '%2%3% to match if 23456 contains 23:

SELECT t1.DOW
     , t2.DOW
     , (t1.DOW LIKE test) AS t1_contains_t2
FROM t1
  JOIN
    ( SELECT t2.DOW
           , CONCAT( '%'
                 , GROUP_CONCAT( SUBSTRING( t2.DOW, num.i, 1 )
                                 ORDER BY num.i
                                 SEPARATOR '%' )
                 , '%'
                 ) AS test
      FROM t2
        JOIN num
          ON (SUBSTRING(t2.DOW,num.i,1) != "" )
      GROUP BY t2.DOW
    ) AS temp ;

The t1_contains_t2 column will have 1 or 0 depending on whether t1.DOW contains t2.DOW

Or you can use WHERE (t1.DOW LIKE test) in the query.

score 0 · Accepted Answer · answered May 01 '11 at 00:31

0

I assume DOW is Day of Week and only has numbers 1-7

SELECT T1.DOW,T2.DOW
FROM T1,T2
WHERE NOT EXISTS(SELECT *
                 FROM (VALUES('%1%'),
                             ('%2%'),
                             ('%3%'),
                             ('%4%'),
                             ('%5%'),
                             ('%6%'),
                             ('%7%')) Nums(N)
                 WHERE (T2.DOW LIKE N AND T1.DOW NOT LIKE N))

SQL Server 2008 answer though AFAIK this is standard SQL.

answered May 01 '11 at 00:31

Martin Smith

438,706
87
741
845

@David - I don't own any copy of the standard a quick google indicates that the `Table Value Constructor` bit is probably not standard - Can easily be replaced with a collection of `UNION ALL`s though. Edit: - On second thoughts this answer indicates that it is SQL 2003 standard http://stackoverflow.com/questions/840254/sql-how-to-create-multi-row-results-with-no-source-table/840550#840550 – Martin Smith May 01 '11 at 00:50
New to this site and don't have enough rep to vote up your answer. You helped me out a lot, thanks. – YetiHandler May 01 '11 at 18:59

score 0 · Answer 4 · answered May 01 '11 at 01:38

First, I want to make sure everybody understands that I firmly believe this should be in a stored procedure. Also, that I will probably spend most of tomorrow trying to figure out a suitable punishment for what I'm about to write.

Let's assume we have two tables. The first table contains the digits we want to search for.

CREATE TABLE search_digits (
  digit integer NOT NULL
);
INSERT INTO search_digits VALUES (2), (3);

We're going to search for the digits 2 and 3.

The second table contains the values we want to search within.

CREATE TABLE dow_digits (
  dow integer NOT NULL,
  digit integer NOT NULL,
  CONSTRAINT dow_digits_pkey PRIMARY KEY (dow, digit),
);
INSERT INTO dow_digits VALUES (23, 2);
INSERT INTO dow_digits VALUES (23, 3);
INSERT INTO dow_digits VALUES (236, 2);
INSERT INTO dow_digits VALUES (236, 3);
INSERT INTO dow_digits VALUES (236, 6);
INSERT INTO dow_digits VALUES (1234, 1);
INSERT INTO dow_digits VALUES (1234, 2);
INSERT INTO dow_digits VALUES (1234, 3);
INSERT INTO dow_digits VALUES (1234, 4);
INSERT INTO dow_digits VALUES (12346, 1);
INSERT INTO dow_digits VALUES (12346, 2);
INSERT INTO dow_digits VALUES (12346, 3);
INSERT INTO dow_digits VALUES (12346, 4);
INSERT INTO dow_digits VALUES (12346, 6);
INSERT INTO dow_digits VALUES (123456, 1);
INSERT INTO dow_digits VALUES (123456, 2);
INSERT INTO dow_digits VALUES (123456, 3);
INSERT INTO dow_digits VALUES (123456, 4);
INSERT INTO dow_digits VALUES (123456, 5);
INSERT INTO dow_digits VALUES (123456, 6);
INSERT INTO dow_digits VALUES (67, 6);
INSERT INTO dow_digits VALUES (67, 7);

We can find at least some of the values for dow that contain the digits 2 and 3 with a simple query.

select d1.dow from dow_digits d1
inner join search_digits d2 on d1.digit = d2.digit
group by dow
having count(distinct d1.digit) = (select count(distinct digit) 
                                   from search_digits);

dow
--
23
236
1234
12346
123456

That seems to work. It's not clear what the OP expects if the search integer is 233, so I'm going to ignore that case for now. I want to finish this quickly, then step in front of a truck.

The next question is, can we build search_digits on the fly? In PostgreSQL, sort of.

SELECT UNNEST(ARRAY[2,3]) as digit;

digit
--
2
3

Drop the table search_digits, and wrap that in a CTE.

with search_digits as (
    select unnest(array[2,3]) as digit
)
select d1.dow from dow_digits d1
inner join search_digits d2 on d1.digit = d2.digit
group by dow
having count(distinct d1.digit) = (select count(distinct digit) 
                                   from search_digits);

dow
--
23
236
1234
12346
123456

Next question. Can we build dow_digits on the fly? In PostgreSQL, sort of. Need to know how many digits in the longest number. Let's say no more than six.

select dow, digit 
from (select dow, unnest(array[substring((dow)::text from 1 for 1),
                               substring((dow)::text from 2 for 1),
                               substring((dow)::text from 3 for 1),
                               substring((dow)::text from 4 for 1),
                               substring((dow)::text from 5 for 1),
                               substring((dow)::text from 6 for 1)]) digit
      from dow ) d
where d.digit <> '';

dow     digit
--
23      2
23      3
236     2
236     3
236     6
1234    1
1234    2
1234    3
1234    4
12346   1
12346   2
12346   3
12346   4
12346   6
123456  1
123456  2
123456  3
123456  4
123456  5
123456  6
67      6
67      7
233     2
233     3
233     3

Pulling all that together into a single statement . . .

with search_digits as (
    select unnest(array[1,2,3,4,6]) digit
)
select dow
from (select dow, digit 
      from (select dow, unnest(array[substring((dow)::text from 1 for 1),
                                   substring((dow)::text from 2 for 1),
                                   substring((dow)::text from 3 for 1),
                                   substring((dow)::text from 4 for 1),
                                   substring((dow)::text from 5 for 1),
                                   substring((dow)::text from 6 for 1)]) digit
            from dow
            ) arr
      where arr.digit <> ''
      ) d
inner join (select distinct digit from search_digits) sd 
        on  sd.digit = d.digit::integer
group by dow
having count(distinct d.digit) = (select count(distinct digit) 
                                  from search_digits)

dow
--
12346
123456

Oh, I can feel karma points slipping away . . . where's that truck?

SQL: Test if Number Contains Digits of Another Number

4 Answers4