获取仅包含某些字符的行 [英] Get rows that contain only certain characters

问题描述

我只想获取列中仅包含某些字符的那些行.
假设列名是 DATA.

I want to get only those rows that contain ONLY certain characters in a column.
Let's say the column name is DATA.

我想获取仅 DATA 中的所有行(必须具有所有三个条件！):

I want to get all rows where in DATA are ONLY (must have all three conditions!):

• 数字字符(1 2 3 4 5 6 7 8 9 0)
• 破折号 (-)
• 逗号 (,)

例如:

值 "10,20,20-30,30" 可以

值 "10,20A,20-30,30Z" 不正常

值 "30" 不正常

值 "AAAA" 不正常

值 "30-" 不正常

值 "30," 不正常

值 "-," 不正确

推荐答案

试试 patindex:

Try patindex:

select * from(
    select '10,20,20-30,30' txt union
    select '10,20,20-30,40' txt union
    select '10,20A,20-30,30Z' txt
)x
where patindex('%[^0-9,-]%', txt)=0

对于您的桌子，请尝试:

For you table, try like:

select 
    DATA
from 
    YourTable
where 
    patindex('%[^0-9,-]%', DATA)=0

根据您新编辑的问题，查询应如下所示:

As per your new edited question, the query should be like:

select 
    DATA
from 
    YourTable
where 
    PATINDEX('%[^0-9,-]%', DATA)=0 and
    PATINDEX('%[0-9]%', LEFT(DATA, 1))=1 and
    PATINDEX('%[0-9]%', RIGHT(DATA, 1))=1 and
    PATINDEX('%[,-][-,]%', DATA)=0 

这篇关于获取仅包含某些字符的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！