JimmyJimm JimmyJimm - 3 months ago 17
Vb.net Question

Get records which field contains specific digits

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

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},%"},
}