模式匹配sql中特定长度的字符串

I need to query for rows from a table where one of columns matches a string whose defining characteristic is an alphanumeric string of specific length (say 4) followed by a ":" followed by an integer.

我需要查询表中的行,其中一列与一个字符串匹配,该字符串的定义特征是一个特定长度的字母数字字符串(比如4),后跟一个“:”后跟一个整数。

pattern : alphanumericstring : integer

pattern:alphanumericstring:integer

example1: 1234:someint
example2: abcd:someotherint

I tried the following

我尝试了以下内容

select * from mytable where col1 like '[]{4}:%' 

select * from mytable where col1 like '.{4}:%'

and neither of these work. I am aware I didn't even try to ensure that the piece following the ":" was an integer.

这些都不起作用。我知道我甚至没有尝试确保“:”后面的部分是一个整数。

3 个解决方案

#1

You can use a combination of charindex, substring and isnumeric

您可以使用charindex,substring和isnumeric的组合

CREATE TABLE MyTable
(
    col1 varchar(20),
    col2 varchar(50)
)

INSERT INTO MyTable
    VALUES 
    ('ABCD:123', 'Value 123'),
    ('1234:1234', 'Value 1234'),
    ('xyz:1234', 'should not be selected'),
    ('cdef:abcd', 'should not be selected too')


SELECT *
FROM MyTable
WHERE CHARINDEX(':', col1, 0) = 5 AND 
      ISNUMERIC(SUBSTRING(col1, CHARINDEX(':', col1) + 1, 20)) = 1

#2

SQL Server doesn't directly support regular expressions (if you search around, you can probably find some tutorials for adding them via user-defined functions).

SQL Server不直接支持正则表达式(如果你搜索,你可以找到一些教程,通过用户定义的函数添加它们)。

LIKE doesn't support quantifiers, but it does have wildcards and lightweight character classes.

LIKE不支持量词,但它确实有通配符和轻量级字符类。

An underscore will match any character:

下划线将匹配任何字符:

SELECT col1
FROM data
WHERE col1 LIKE '____:%';

Or you can specify range(s) of characters to match:

或者您可以指定要匹配的字符范围:

SELECT col1
FROM data
WHERE col1 LIKE '[a-z0-9][a-z0-9][a-z0-9][a-z0-9]:%';

See these live on SQLFiddle.

在SQLFiddle上看到这些。

To specify that the second part must consist of digits only, an additional condition could be used:

要指定第二部分必须仅包含数字,可以使用附加条件:

SELECT col1
FROM data
WHERE col1     LIKE '[a-z0-9][a-z0-9][a-z0-9][a-z0-9]:%'
  AND col1 NOT LIKE '[a-z0-9][a-z0-9][a-z0-9][a-z0-9]:%[^0-9]%';

You can test the last one live as well.

你也可以测试最后一个。

#3

It is ugly, but this should solve your problem for positive integers:

这很难看,但这应该可以解决正整数的问题:

;WITH test AS (
  SELECT expr = '1234:3421'
  UNION ALL SELECT '1234:25'
  UNION ALL SELECT '1234:xx')
select * 
from test
where 
  expr like '%:[0-9]'
  OR expr like '%:[0-9][0-9]'
  OR expr like '%:[0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  OR expr like '%:[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

#1