***求查询特殊字符过程***

[bigmingming]

表
RuleNo NOTE
***ABCU###123,***ABCU###456 A
***ABCU###123,***ABCU###456 T
***ABCU###123 B
.....

例如，设置规则，规则之间用逗号分开,RuleNo字段中的
***ABCU###123
按约定*代表英文字母，#代表数字,其他的字符就是实际的字符，字母都是大字,使用SQL 2008，想用[A-Z][0-9]查找方法是否合理


查询字符串为
CFEABCU789123

结果应该找到3条
NOTE
A
T
B

[还在加载中灬]

;WITH RuleTB(RuleNo,NOTE)AS(
	SELECT '***ABCU###123,***ABCU###456','A'
	UNION ALL SELECT '***ABCU###123,***ABCU###456','T'
	UNION ALL SELECT '***ABCU###123','B'
)
--以上模拟你的规则表
--以下开始查询
,CTE AS(
	SELECT NOTE,RuleNo
		,CAST('<V>'+REPLACE(RuleNo,',','</V><V>')+'</V>' AS XML)RuleNoS
	FROM RuleTB
)
SELECT NOTE FROM CTE T1
WHERE EXISTS
	(SELECT 1 FROM(SELECT N.V.value('.','VARCHAR(20)')V FROM T1.RuleNoS.nodes('/V') N(V))T2
	WHERE 'CFEABCU789123' LIKE REPLACE(REPLACE(V,'#','[0-9]'),'*','[A-Z]'))

[还在加载中灬]

;WITH RuleTB(RuleNo,NOTE)AS(
	SELECT '***ABCU###123,***ABCU###456','A'
	UNION ALL SELECT '***ABCU###123,***ABCU###456','T'
	UNION ALL SELECT '***ABCU###123','B'
)
SELECT NOTE FROM RuleTB
WHERE EXISTS
	(SELECT 1 FROM master..spt_values
	WHERE type='P'AND number>0 AND number*13<=LEN(RuleNo)
		AND 'CFEABCU789123' LIKE REPLACE(REPLACE(SUBSTRING(RuleNo,(number-1)*14+1,number*14-1),'#','[0-9]'),'*','[A-Z]'))

[还在加载中灬]

;WITH RuleTB(RuleNo,NOTE)AS(
	SELECT '***ABCU###123,***ABCU###456','A'
	UNION ALL SELECT '***ABCU###123,***ABCU###456','T'
	UNION ALL SELECT '***ABCU###123','B'
)
SELECT NOTE FROM RuleTB
WHERE EXISTS
	(SELECT 1 FROM master..spt_values
	WHERE type='P'AND number>0 AND number*14-1<=LEN(RuleNo)
		AND 'CFEABCU789123' LIKE REPLACE(REPLACE(SUBSTRING(RuleNo,(number-1)*14+1,number*14-1),'#','[0-9]'),'*','[A-Z]'))

纠正一下第二种写法

[中国风]

declare  @ta table(RuleNo varchar(100),NOTE varchar(2))
insert into @ta
    SELECT '***ABCU###123,***ABCU###456','A'
    UNION ALL SELECT '***ABCU###123,***ABCU###456','T'
    UNION ALL SELECT '***ABCU###123','B'

declare @Str varchar(50)='CFEABCU789123'
select NOTE
FROM master..spt_values as a,@ta as b 
    WHERE type='P'AND a.number>0  and charindex(',',','+b.RuleNo,a.number)=a.number
    and @Str like replace(replace(substring(b.RuleNo,a.number,charindex(',',b.RuleNo+',',a.number)-a.number),'*','[a-zA-Z]'),'#','[0-9]')

[bigmingming]

谢谢各位，如果查询字符串的位数和规则位数都不定，怎么查询

[还在加载中灬]

引用 5 楼 bigmingming 的回复:

谢谢各位，如果查询字符串的位数和规则位数都不定，怎么查询

用#1或#4都可以处理

[bigmingming]

多谢 请问 SELECT N.V.value('.','VARCHAR(50)')V FROM T1.RuleNoS.nodes('/V') N(V))T2 有关这些知识点 在哪里找到

[还在加载中灬]

关键字 SQL XML 你可以搜一下 介绍两个相关的 http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html XML http://www.cnblogs.com/myjece/archive/2009/12/20/1628525.html XML拆分多行