网站首页 > 服务与支持 > 常见问题

药易通拼音码重算脚本

2018-06-04 20:29:57 岳阳诺尔软件科技有限公司 阅读

DECLARE @P_id INT, @Str NVARCHAR(200), @Pym NVARCHAR(200), @Word NCHAR(1)

DECLARE StrToPym SCROLL CURSOR FOR

   SELECT product_id,name FROM dbo.products ORDER BY product_id

OPEN StrToPym

FETCH NEXT FROM StrToPym INTO @P_id,@Str

WHILE @@FETCH_STATUS = 0

BEGIN

   SET @Pym = ''

   SET @Word = ''

   WHILE LEN(@Str) > 0

   BEGIN

     SET @Word = LEFT(@Str,1)

     SET @Pym = @Pym + ( CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968 + 20901 THEN 

                       ( SELECT TOP 1 PY FROM 

                       ( SELECT 'A' AS PY , N'驁' AS Word

UNION ALL

SELECT 'B' ,N'簿'

UNION ALL

SELECT 'C' ,N'錯'

UNION ALL

SELECT 'D' ,N'鵽'

UNION ALL 

SELECT 'E' ,N'樲'

UNION ALL

SELECT 'F' ,N'鰒'

UNION ALL

SELECT 'G' ,N'腂'

UNION ALL

SELECT 'H' ,N'夻'

UNION ALL

SELECT 'J' ,N'攈'

UNION ALL

SELECT 'K' ,N'穒'

UNION ALL

SELECT 'L' ,N'鱳'

UNION ALL

SELECT 'M' ,N'旀'

UNION ALL

SELECT 'N' ,N'桛'

UNION ALL

SELECT 'O' ,N'漚'

UNION ALL

SELECT 'P' ,N'曝'

UNION ALL

SELECT 'Q' ,N'囕'

UNION ALL

SELECT 'R' ,N'鶸'

UNION ALL

SELECT 'S' ,N'蜶'

UNION ALL

SELECT 'T' ,N'籜'

UNION ALL

SELECT 'W' ,N'鶩'

UNION ALL

SELECT 'X' ,N'鑂'

UNION ALL

SELECT 'Y' ,N'韻'

UNION ALL

SELECT 'Z' ,N'咗'

                       ) T WHERE Word >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS ORDER BY PY ASC

                       ) ELSE UPPER(@Word) END 

                       ) 

     SET @Str = RIGHT(@Str, LEN(@Str)-1)

   END

   UPDATE dbo.products SET pinyin = @Pym WHERE product_id = @P_ID 

   FETCH NEXT FROM StrToPym INTO @P_id,@Str

END

CLOSE StrToPym

DEALLOCATE StrToPym


岳阳诺尔软件科技有限公司 版权所有 2008-2014 湘ICP备12010268号