;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]'))
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]'))
解决方案 »
- sql server2005 如何判断表中是否有数据
- 如何从Excel中导入数据到SQL数据库中的指定表中
- 怎么用where 做外连接。来根据某个表显示数据
- 怎样获取到SQL调用系统存储过程的所有信息
- 这句话的日期部分该怎么表达:rs.open("select * from info where flag>=1 and sortid is not null and dateandtime=""&date&"""),conn,
- 求存储过程写法
- 简单的小问题!急!!
- 我的数据库有15个活动连接(sleeping状态)---是不是正常的?会不会有什么性能问题?
- 一个SQL命题!(100分)
- 请高手讲一讲sqlserver中的数据转换服务
- 动态SQL不理解
- 关于查询的问题
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]'))
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]'))纠正一下第二种写法
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]')
请问
SELECT N.V.value('.','VARCHAR(50)')V FROM T1.RuleNoS.nodes('/V') N(V))T2
有关这些知识点 在哪里找到
http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html XML
http://www.cnblogs.com/myjece/archive/2009/12/20/1628525.html XML拆分多行