0

The question is fairly clear. I wanna check if for example a number x is contained in another number y. And I wanna do it in SQL (also in LINQ if possible). Additionally those integer values(y values) are Ids from a table. So when LINQ is concerned, I am not looking for some general solution like:

int y=123,x=32;
bool isContainedInY = y.ToString().Contains(x.ToString());

Examples:

x = 5, y = 1256 => true
x = 384, y = 38412 => true
x = 1, y = 5236 => false

Answers can be based on a table like the following:

+------+-------+
| Id   | Name  | 
+------+-------+
| 52   | Lola  | 
| 65   | Dolly | 
| 88   | Wolly |
| 102  | Sorry |
+------+-------+
Giray Sekerlen
  • 91
  • 1
  • 7
  • 1
    What have you tried so far. There are convert and string functions that will give you what you need. – Peter Smith Feb 04 '20 at 08:21
  • 1
    Numbers don't contain other numbers, strings do. A simple `LIKE '%5%'` will work. In LINQ, `String.Contains(substring)` is translated to `LIKE '%substring%'` – Panagiotis Kanavos Feb 04 '20 at 08:23
  • The question isn't clear though - what are you trying to do though? This is a *very* slow operation that can't be accelerated by indexes. It will scan the entire table to check every value. Whatever it is you want to do, there are probably better ways to do it. If you want to use a number as a set of flags, don't. You can use separate fields, as small as a single byte, that are easy to query and index – Panagiotis Kanavos Feb 04 '20 at 08:26
  • No @SurajKumar that does not answer my question at all. I already know everything that is given as an answer in that question. It is easy with strings. – Giray Sekerlen Feb 04 '20 at 08:59
  • You alternatively need to convert your integer value as string for comparison because Is 500 contains 5 but not equal means not equal but a part only. You can easily convert both values into string and compare. Also you can find using charindex, like operator and using contains all are there. It may be your choice you do not want to consider those answers. – Suraj Kumar Feb 04 '20 at 09:04
  • @PanagiotisKanavos What I am trying do to is finding certain Id values in a table which contain a given integer value as a part of it. – Giray Sekerlen Feb 04 '20 at 09:04
  • 1
    @GiraySekerlen why? That's what you wrote in the question itself, but *why*? What real problem are you trying to solve by searching for one number inside another? – Panagiotis Kanavos Feb 04 '20 at 09:13
  • I was doing it til today for string values but then I received a task today which required me to bring all the Id values of a table based on an integer value as part of those Ids. If the type of the values were string I would easily use `value1.Contains(value2)` but that does not work for integer values. – Giray Sekerlen Feb 04 '20 at 09:56

4 Answers4

3

You can cast them to varchar first

SELECT CHARINDEX(STR(x), STR(y)) > 0
kkica
  • 4,034
  • 1
  • 20
  • 40
2

You can use the following, using CHARINDEX:

SELECT CASE WHEN CHARINDEX(CONVERT(VARCHAR, x), CONVERT(VARCHAR, y)) > 0 THEN 1 ELSE 0 END

-- or
SELECT CONVERT(BIT, CHARINDEX(CONVERT(VARCHAR, x), CONVERT(VARCHAR, y)))

... or using LIKE:

SELECT CASE WHEN CONVERT(VARCHAR, y) LIKE '%' + CONVERT(VARCHAR, x) + '%' THEN 1 ELSE 0 END

... or using PATINDEX:

SELECT CASE WHEN PATINDEX('%' + CONVERT(VARCHAR, x) + '%', CONVERT(VARCHAR, y)) > 0 THEN 1 ELSE 0 END

-- or
SELECT CONVERT(BIT, PATINDEX('%' + CONVERT(VARCHAR, x) + '%', CONVERT(VARCHAR, y)))

... or using REPLACE:

SELECT CASE WHEN REPLACE(CONVERT(VARCHAR, y), CONVERT(VARCHAR, x), '') <> y THEN 1 ELSE 0 END

You can also create a function to check if a number contains another number:

CREATE FUNCTION dbo.ContainsNumber (@y INT, @x INT)  
RETURNS BIT
AS
BEGIN
  RETURN CONVERT(BIT, CHARINDEX(CONVERT(VARCHAR, @x), CONVERT(VARCHAR, @y)))
END

You can use the function like this:

SELECT dbo.ContainsNumber(y, x)
SELECT * FROM table_name WHERE dbo.ContainsNumber(y, x)

demo on dbfiddle.uk

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
1

Like operator will solve your problem. But first, you need to convert int variable to string.

DECLARE @x INT = 5;
DECLARE @y INT = 1256;

if CONVERT(VARCHAR, @y) like '%'+CONVERT(VARCHAR, @x)+'%'
begin
    select 1 as result
end 
else
begin
    select 0 result
end
Prajakta Kale
  • 392
  • 3
  • 19
1

In LINQ Demo on dotnet fildde

You can use Contains() method in Linq to achieve it.

private static bool CheckContain(int x, int[] array)
{
    return array.Any(y => y.ToString().Contains(x.ToString()));
}

Testing

    var array = new [] { 52, 65, 88, 102, 5 };

    Console.WriteLine(CheckContain(5, array)); // True
    Console.WriteLine(CheckContain(384, array)); // False
    Console.WriteLine(CheckContain(1, array )); // True
Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56