如何在 searchWord 存储过程中使用 NormalizeChars 存储过程

Question

我想

ALTER PROCEDURE [dbo].[SearchWord] (@Word            NVARCHAR(50),
                                    @PageNumber      INT,
                                    @ProductsPerPage INT,
                                    @HowManyResults  INT OUTPUT)
AS
  SET @Word = '%' + RTRIM(@Word) + '%';

  DECLARE @Results TABLE (
    ProductsId          INT,
    ProductsCode        NVARCHAR(250),
    ProductsDesc        NVARCHAR(MAX),
    ProductsIngredients NVARCHAR(MAX),
    ProductsName        NVARCHAR(250),
    ProductsPhoto       NVARCHAR(MAX),
    ProductsPrice       MONEY,
    ProductsWeight2     FLOAT,
    RowNumber           INT)

  -- Obtain the matching products
  INSERT INTO @Results
  SELECT DISTINCT T1.ProductsID,
                  T1.ProductsCode,
                  LOWER(T1.ProductsDesc)                      asProductsDesc,
                  T1.ProductsIngredients,
                  LOWER(T1.ProductsName)                      AS ProductsName,
                  T1.ProductsPhoto,
                  T1.ProductsPrice,
                  T1.ProductsWeight2,
                  ROW_NUMBER() OVER(ORDER BY T1.ProductsName) RowNumber
  FROM   (SELECT ProductsID,
                 ProductsCode,
                 LOWER(ProductsDesc) AS ProductsDesc,
                 ProductsIngredients,
                 LOWER(ProductsName) AS ProductsName,
                 ProductsPhoto,
                 ProductsPrice,
                 ProductsWeight2
          FROM   Products
          WHERE  ProductsName LIKE @Word) AS T1
         FULL OUTER JOIN (SELECT *
                          FROM   Products
                          WHERE  ProductsDesc LIKE @Word) AS T2
           ON T1.ProductsID = T2.ProductsID
  ORDER  BY T1.ProductsPrice

  DELETE FROM @Results
  WHERE  NULLIF([ProductsName], '') IS NULL

  SELECT @HowManyResults = COUNT(*)
  FROM   @Results

  INSERT INTO @Results
  SELECT DISTINCT ProductsId,
                  ProductsCode,
                  ProductsDesc,
                  ProductsIngredients,
                  LOWER(ProductsName) AS ProductsName,
                  ProductsPhoto,
                  ProductsPrice,
                  ProductsWeight2,
                  RowNumber
  FROM   @Results

  SELECT DISTINCT ProductsId,
                  ProductsCode,
                  ProductsDesc,
                  ProductsIngredients,
                  LOWER(ProductsName) AS ProductsName,
                  ProductsPhoto,
                  ProductsPrice,
                  ProductsWeight2,
                  RowNumber
  FROM   @Results R
  WHERE  R.RowNumber > ( @PageNumber - 1 ) * @ProductsPerPage
         AND R.RowNumber <= @PageNumber * @ProductsPerPage
  ORDER  BY R.ProductsPrice ASC  

使用此过程

ALTER PROCEDURE normalizeChars 
(@NAME NVARCHAR(100)) 
AS
BEGIN
  DECLARE @TempString NVARCHAR(100)
  SET @TempString = @NAME 
  SET @TempString = LOWER(@TempString)
  SET @TempString =  REPLACE(@TempString,'à', 'a')
  SET @TempString =  REPLACE(@TempString,'è', 'e')
  SET @TempString =  REPLACE(@TempString,'é', 'e')
  SET @TempString =  REPLACE(@TempString,'ì', 'i')
  SET @TempString =  REPLACE(@TempString,'ò', 'o')
  SET @TempString =  REPLACE(@TempString,'ù', 'u')
  SET @TempString =  REPLACE(@TempString,'ç', 'c')
  SET @TempString =  REPLACE(@TempString,'''', '')
  SET @TempString =  REPLACE(@TempString,'`', '')
  SET @TempString =  REPLACE(@TempString,'-', '')
  SET @TempString =  REPLACE(@TempString,'ά','α')
  SET @TempString =  REPLACE(@TempString,'έ','ε')
  SET @TempString =  REPLACE(@TempString,'ί','ι')
  SET @TempString =  REPLACE(@TempString,'ό','ο')
  SET @TempString =  REPLACE(@TempString,'ή','η')
  SET @TempString =  REPLACE(@TempString,'ύ','υ')
  RETURN @TempString
END

on ProductsName and ProductsDesc

从该函数中删除变音符号是否可能以及如何实现？

Answer 1

您说您在 Greek_CI_AI 上。在这种情况下，大部分映射已经发生（只有前 3 个映射没有发生）。

;WITH T(col1,col2) AS
(
  SELECT N'''',N'' UNION ALL
  SELECT N'`',N'' UNION ALL
  SELECT N'-',N'' UNION ALL

  SELECT N'à',N'a' UNION ALL
  SELECT N'è',N'e' UNION ALL
  SELECT N'é',N'e' UNION ALL
  SELECT N'ì',N'i' UNION ALL
  SELECT N'ò',N'o' UNION ALL
  SELECT N'ù',N'u' UNION ALL
  SELECT N'ç',N'c' UNION ALL
  SELECT N'ά',N'α' UNION ALL
  SELECT N'έ',N'ε' UNION ALL
  SELECT N'ί',N'ι' UNION ALL
  SELECT N'ό',N'ο' UNION ALL
  SELECT N'ή',N'η' UNION ALL
  SELECT N'ύ',N'υ'
)
SELECT *
FROM T 
WHERE col1<>col2  COLLATE Greek_CI_AI 

返回

col1 col2
---- ----
'    
`    
-    

此外，您不需要使用 LOWER，因为排序规则不区分大小写。

如果您确实需要忽略搜索中剩余的​​ 3 个字符，您可以使用标量 UDF，如下所示

CREATE FUNCTION dbo.normalizeChars 
(@Name NVARCHAR(100)) 
RETURNS NVARCHAR(100)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS 
BEGIN
  SET @Name =  REPLACE(@Name,'''', '')
  SET @Name =  REPLACE(@Name,'`', '')
  SET @Name =  REPLACE(@Name,'-', '')
  RETURN @Name
END