0

The first column of a table contains some Ids and the values in the other columns are the numbers corresponded to those Ids. Considering some special numbers, we want to select the rows that this special numbers are among the corresponded numbers to Ids. For example, let we have the following table and the special numbers are 3,5. We want to select the rows in which 2,5 are among the columns except Id:

| Id | corresponded numbers
|----|----------------------
| 1  | 2 | 3 | 5 |
| 2  | 1 | 5 |
| 3  | 1 | 2 | 4 | 5 | 7 |
| 4  | 3 | 5 | 6 |

Therefore, we want to have the following table as the result:

| Id | corresponded numbers
|----|----------------------
| 1  | 2 | 3 | 5 |
| 3  | 1 | 2 | 4 | 5 | 7 |

Would you please introduce me a function in Excel or a query in SQL to do the above selection?

AmHsnSharafi
  • 41
  • 4

1 Answers1

0
SELECT id,
       [corresponded numbers]
FROM TableName
WHERE (charIndex('2', [corresponded numbers]) > 0
       AND charIndex('5', [corresponded numbers]) > 0)
Teja Goud Kandula
  • 1,462
  • 13
  • 26
  • Please [edit] your post to include an explanation – mousetail Oct 06 '21 at 09:12
  • Thanks @Teja Goud Kandula. Should I merge the columns which are under the title "corresponded number" to one column in which the numbers are disjointed by commas? – AmHsnSharafi Oct 06 '21 at 11:02
  • If you are using SQL Serve you can use `STRING_AGG` and the separator can be a comma or pipe based on your preference. If you are using any other database check for the equivalent function for that database. – Teja Goud Kandula Oct 06 '21 at 11:21