Get records which field contains specific digits

Question

Having some field in table that could contain between either 1, 2 or 3 digits separated by commas. So as follows example below:

ID | digitField
1  |   4, 55
2  |   1 ,70,33
3  |   8 
4  |   9,22,43
5  |   1,833,3
6  |   77,9
7  |   33
8  |   2,3335
9  |   6, 33

Now having some digit i would like to search and get ID of the records which containing it. What will be the best approach to search for those records?

For instance i am seeking for every occurrence of digit 33. In this case i would like to get those records based on above example:

2  |   1,70,33
7  |   33
9  |   6, 33

Does following query would be good enough and not risky to get wrong results, or could you give better solution?:

SELECT ID FROM table WHERE CONTAINS(digitField, '33');

This query is very important as result ids will delete shop products so i would like to avoid wrong results. Note that in our case records containing 3, 833 or 3335 cannot match as we looking for 33. Note also that the could be some spaces between within that field (that's something which is already implemented and have no chance to change it)

Looking for best solution.

Additional question to correctly close up query within string:

Using cnn As New SqlConnection(strcon)
cnn.Open()
'--Just in case remove all spaces from string in field Materials before
Using dad As New SqlDataAdapter(";WITH yourtable as ( SELECT ID, REPLACE(digitField, ' ', '') digitfield FROM tempDigit ) 
                        SELECT * FROM YourTable WHERE digitField = @matId
                        OR digitField LIKE @matId+',%'
                        OR digitField LIKE '%,'+@matId
                        OR digitField LIKE '%,'+@matId+',%'", cnn)
    dad.SelectCommand.Parameters.Add(New SqlParameter("@matId", matId.ToString))
    dad.Fill(dt)
    If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
        result = dt
    End If
End Using
cnn.Close()
End Using

Answer 1

Just filter all possible variations of given number's occurences. If field can contains spaces - just remove them.

SELECT *
FROM YourTable
CROSS APPLY (SELECT REPLACE(digitField, ' ', '') AS pureField) digit
WHERE digit.pureField = @Exact        --if only one number in field
   OR digit.pureField LIKE @Begin   --if number in the beginning of text
   OR digit.pureField LIKE @End   --if number in the end of text
   OR digit.pureField LIKE @Middle --if number in the middle of text

Sql parameters creating

Dim parameters As SqlParameter() = 
{
    New SqlParameter With {.ParameterName = "@Exact", .SqlDbType = SqlDbType.VarChar, .Value = matId.ToString()},
    New SqlParameter With {.ParameterName = "@Begin", .SqlDbType = SqlDbType.VarChar, .Value = $"{matId},%"},
    New SqlParameter With {.ParameterName = "@End", .SqlDbType = SqlDbType.VarChar, .Value = $"%,{matId}"},
    New SqlParameter With {.ParameterName = "@Middle", .SqlDbType = SqlDbType.VarChar, .Value = $"%,{matId},%"},
}

Answer 2

you need a table valued SPLIT function to "unpivot" your digitField into records, so

ID | digitField
1  |   4, 55
2  |   1 ,70,33
3  |   8 

will become

ID | n  | digitField
1  | 1  | 4
1  | 2  | 55
2  | 1  | 1
2  | 2  | 70
2  | 3  | 33
3  | 1  | 8 

then you will search for your number into this result.

There are many split functions around, you can google for them.
I have developed mine by myself:

CREATE FUNCTION [dbo].[FN_SPLIT]
/*
  MTW - FN_SPLIT()
  mtw@inbox.lv
*/
(
    @Line varchar(8000),
    @SplitOn varchar(10) = ','
)
RETURNS @RtnValue table
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- VERY USEFUL TO MAKE JOINS AND TO CREATE THE UNIQUE INDEX ON SPLITTED STRINGS
    Data varchar(800) NOT NULL,
    UNIQUE (DATA, ID) -- THIS WILL MAKE REALLY FASTER THE QUERIES USING THIS FUNCTION
)
AS
BEGIN
    IF @Line IS NULL RETURN

    DECLARE @split_on_len INT = LEN(@SplitOn+'X')-1 -- TO CATCH TRAILING SPACES PROBLEM WITH LEN()
    DECLARE @line_len INT = LEN(@line+'X')-1
    DECLARE @start_at INT = 1
    DECLARE @end_at INT
    DECLARE @data_len INT

    WHILE 1=1
    BEGIN
        IF @start_at > @line_len BREAK;
        SET @end_at = CHARINDEX(@SplitOn, @Line, @start_at)
        SET @data_len = CASE @end_at WHEN 0 THEN @line_len ELSE @end_at-@start_at END
        INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len));
        SET @start_at = @start_at + @data_len + @split_on_len
    END

    RETURN
END

so your query will be:

;WITH
S AS (
    SELECT T.ID, X.Id N, RTRIM(LTRIM(X.Data)) digitField
    FROM T
    CROSS APPLY (
        SELECT *
        FROM FN_SPLIT(T.digitField, ',') F
    ) X
)
SELECT *
FROM S
WHERE digitField = '33'
ORDER BY 1,2

the result is:

ID  N   digitField
2   3   33
7   1   33
9   2   33

where column N is the position of your number in the original digitField column

Answer 3

Using CHARINDEX you can get the matching result:

SELECT *
FROM Table
WHERE CHARINDEX('33', digitField) >= 1;

Sample execution with the given sample data:

DECLARE @TestTable TABLE (ID INT, digitField VARCHAR (200));

INSERT INTO @TestTable (ID, digitField) VALUES
(1, '4, 55'),
(2, '1,70,33'),
(3, '8'), 
(4, '9,22,43'),
(5, '1,2,3'),
(6, '77,9'),
(7, '33'),
(8, '2,5'),
(9, '6, 33');

SELECT *
FROM @TestTable
WHERE CHARINDEX('33', digitField) >= 1;

Output:

ID  digitField
--------------
2   1,70,33
7   33
9   6, 33