Skip to content
Advertisement

How to select combination of 4 digits number from table columns?

I have a table as below:-

id | columnA | columnB | columnC
1    7234        3432    3245
2    1237        1123    6546
3    4421        9371    7324
4    1244        1424    4237

How can I select combination records when I pass in “2374” in my select statement?

My expected result should be

id | columnA | columnB | columnC
1    7234        3432    3245
3    4421        9371    7324
4    1244        1424    4237

Advertisement

Answer

It is not easy to read, but it works:

set @s = '2374';
select * from tablename
where id in (
  select distinct t1.id
  from (
    select t.id, t.abc, group_concat(t.d order by t.d separator '') col
    from (
      select id, abc, substring(col, n, 1) d
      from (
        select id, 'A' abc, columnA col from tablename union all
        select id, 'B', columnB from tablename union all
        select id, 'C', columnC from tablename
      ) t cross join (
        select 1 n union all select 2 union all select 3 union all select 4  
      ) v  
    ) t  
    group by t.id, t.abc
  ) t1 inner join (
    select group_concat(t.d order by t.d separator '') s
    from (
      select substring(@s, 1, 1) d union all select substring(@s, 2, 1) union all 
      select substring(@s, 3, 1) union all select substring(@s, 4, 1) 
    ) t
  ) t2 on t2.s = t1.col 
) 

What this code does is:
sorts the digits inside the column’s value, so 4237 becomes 2347 and compares against the (also) sorted sorted 2374–> 2347.
For this sorting I used group_concat().
See the demo.
Results:

| id  | columnA | columnB | columnC |
| --- | ------- | ------- | ------- |
| 1   | 7234    | 3432    | 3245    |
| 3   | 4421    | 9371    | 7324    |
| 4   | 1244    | 1424    | 4237    |
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement