0

How do I get number field that contain 878 as reflected in the result. It should contain two 8 and one 7.

Edit : using TSQL

Number
878
780
788
700
Result
878
788
JamesT
  • 1
  • 2

3 Answers3

2

There are only 3 combinations so what about the crudest of them all?

Number = 788 OR Number = 878 OR Number = 887

Or even:

Number IN (788,878,887)

If numbers are not just 3 digits then cast the number as string and then:

NumberAsString LIKE '%887%' OR NumberAsString LIKE '%878%' OR NumberAsString LIKE '%788%'
tymtam
  • 31,798
  • 8
  • 86
  • 126
2

A three digit number consisting of exactly one seven and two eights:

where number in (788, 878, 887)

Everything else would be overkill for this simple task.

If the task were different, say, the number can have more digits and must contain exactly one seven and two eights, then we could use an appropriate combination of LIKE and NOT LIKE to get what we are looking for. E.g:

where number like '%7%' -- contains a 7
  and number like '%8%8%' -- contains two 8s
  and number not like '%7%7%' -- doesn't contain more than one 7
  and number not like '%8%8%8%' -- doesn't contain more than two 8s
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
1

UPDATE: This is not a good solution, in stead I would go for the next solution suggested here.

If you are using MySQL REGEX is your friend:

SELECT * FROM _TABLE_ 
WHERE `Number` REGEXP "[7]{1}" AND `Number` REGEXP "[8]{2}";

More info: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

I think in SQL Server should be something like this:

SELECT * FROM _TABLE_ 
WHERE `Number` LIKE '%[7]{1}%' AND `Number` LIKE '%[2]{1}%';

More info: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

enriqueojedalara
  • 74
  • 6