ASP+SQL2000多條件模糊查詢

有種做法叫 拆分元素

也就是 多值字段轉成多行，那麽就很好處理了

按妳的數據 下面是語句 希望對妳有幫助

這裏也借助壹張數字表 來取得 XX字段','的位置

--創建測試表

CREATE TABLE Test

(

Id int primary key identity(1,1),

title int,

xx varchar(2000),

)

--插入數據

INSERT INTO Test

SELECT 22,'12,23,44,55'

UNION ALL SELECT 22,'122,13,44,55'

UNION ALL SELECT 22,'12,22,43,51'

UNION ALL SELECT 22,'1,13,44,55'

UNION ALL SELECT 22,'12,23,24,25'

UNION ALL SELECT 22,'12,33,44,55'

UNION ALL SELECT 22,'2,3,44,55'

UNION ALL SELECT 22,'23,1,2,3'

--建個數字輔助表

CREATE TABLE Nums

(

NumID int primary key

)

--插入數據

DECLARE @InitNum int

SET @InitNum =1

WHILE @InitNum<=100

BEGIN

INSERT INTO Nums

SELECT @InitNum

SET @InitNum=@InitNum+1

END

--開始查詢--mssql的SUBSTRING開始索引是從1開始的

SELECT id,title,xx,numid,SUBSTRING( xx ,numid,CHARINDEX(',',xx+',',numid)-numid )AS '單獨元素'

FROM Test JOIN Nums

ON NumID <= LEN(xx)

AND SUBSTRING(','+xx,NumID,1)=','