如何找出以逗點隔開的字串中,符合每個項目的資料

假設有一字串'Western,Northern',要如何找出特定資料行中含有Western和Northern的資料。

很多時候我們會需要把一個由逗點隔開(例如”Eastern,Northern”)的字串拿來在WHERE條件中,利用IN來找出符合字串中任何一個項目的資料,舉例來說,若想要在Northwind的Region資料表中,找出RegionDescription為Eastern,Northern的資料該怎麼做,本文將就此需求進行探討。

  • 以下程式碼示範嘗試查詢Region資料表中RegionDescription為Western和Northern的資料,當然直接把參數拿到IN敘述是不WORK的,所以查不出任何資料。
   1:  DECLARE @RegionDesc varchar(50)
   2:  SET @RegionDesc = 'Western,Northern'
   3:   
   4:  SELECT *
   5:  FROM Region
   6:  WHERE RegionDescription IN (@RegionDesc)

 

image

針對上述需求,本文提供兩種可能的做法來達到資料查詢的需求,分別說明如下:

  • 以下程式碼示範利用LOOP來重組查詢參數,並搭配EXECUTE(EXEC)來執行T-SQL敘述。
   1:  DECLARE @RegionDesc varchar(50)
   2:  SET @RegionDesc = 'Western,Northern'
   3:   
   4:  --利用LOOP重新組字串
   5:  DECLARE @Delimiter CHAR(1) = ','
   6:  DECLARE @idx INT = 0
   7:  DECLARE @Expression    VARCHAR(1024)  = ''
   8:  SET @idx = CHARINDEX(@Delimiter,@RegionDesc)
   9:  WHILE @idx > 0
  10:      BEGIN
  11:          IF @Expression = ''
  12:              SET @Expression = '''' + SUBSTRING(@RegionDesc,1,@idx - 1) + ''''
  13:          ELSE
  14:              SET @Expression = @Expression + ',''' + SUBSTRING(@RegionDesc,1,@idx - 1) + ''''
  15:          SET @RegionDesc = SUBSTRING(@RegionDesc,@idx + 1,LEN(@RegionDesc) - @idx)
  16:          SET @idx = CHARINDEX(@Delimiter,@RegionDesc)    
  17:      END
  18:      
  19:  IF @Expression = ''
  20:      SET @Expression = '''' + @RegionDesc + ''''
  21:  ELSE
  22:      SET @Expression = @Expression + ',''' + @RegionDesc + ''''
  23:   
  24:  --利用EXECUTE執行T-SQL敘述   
  25:   EXEC ('select * from region where RegionDescription in (' + @Expression + ')')  
  • 以下程式碼示範利用SQL Dynamic IN來查詢符合查詢參數的資料,使用這個方法必須先在字串前後個加上一個逗點,接著用查詢參數為主要查詢對象,找出所有RegionDescription資料行符合查詢參數的資料。
   1:  DECLARE @r2 varchar(50) =  ',Western,Northern,'
   2:  SELECT *
   3:  FROM Region
   4:  WHERE  @r2 LIKE '%,' + RTRIM(LTRIM(RegionDescription)) + ',%'

 

【注意】

建議以EXEC執行T-SQL敘述前須對字串進行驗證,以避免SQL 資料隱碼的問題。

 

【參考資料】