I have a nvarchar field with 4 digits. The first 2 digits are between 00 and 99. The next 2 digits should be a number between 00 and 15
我有一个4位数的nvarchar字段。前两位数在00到99之间。接下来的两位数应该是00到15之间的数字
My SQl like looks like this:
我的SQl是这样的:
I4020 like '[0-9][0-9][0-1][0-9]'
The problem is that e.g. 1219 is possible.
问题是1219是可能的。
Is it possible to do this with the like pattern?
有可能用类似的模式来做吗?
BR Stefan
BR Stefan
3 个解决方案
#1
4
It's not possible to do this with one LIKE pattern. However, you can use two of them:
这样做是不可能的。然而，你可以使用其中的两个:
(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')
Alternatively, if the field always contains digits, you can use numerical matching:
或者，如果字段总是包含数字，可以使用数值匹配:
I4020 LIKE '[0-9][0-9][0-9][0-9]'
AND CONVERT(int, RIGHT(I4020, 2)) <= 15
Note, though, that T-SQL AND does not short-circuit: The latter query might fail if the two rightmost letters of I4020 are not numeric.
但是请注意，T-SQL并没有短路:如果I4020的两个最正确的字母不是数字的话，后一个查询可能会失败。
#2
2
Brake intervals
制动时间间隔
(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')
#3
0
(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')
(I4020像“0[0 - 9][0 - 9][0 - 9]”或I4020 '[0 - 9][0 - 9]1[0 - 5]”)
#1
4
It's not possible to do this with one LIKE pattern. However, you can use two of them:
这样做是不可能的。然而，你可以使用其中的两个:
(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')
Alternatively, if the field always contains digits, you can use numerical matching:
或者，如果字段总是包含数字，可以使用数值匹配:
I4020 LIKE '[0-9][0-9][0-9][0-9]'
AND CONVERT(int, RIGHT(I4020, 2)) <= 15
Note, though, that T-SQL AND does not short-circuit: The latter query might fail if the two rightmost letters of I4020 are not numeric.
但是请注意，T-SQL并没有短路:如果I4020的两个最正确的字母不是数字的话，后一个查询可能会失败。
#2
2
Brake intervals
制动时间间隔
(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')
#3
0
(I4020 like '[0-9][0-9]0[0-9]' OR I4020 like '[0-9][0-9]1[0-5]')
(I4020像“0[0 - 9][0 - 9][0 - 9]”或I4020 '[0 - 9][0 - 9]1[0 - 5]”)