在查询中将0-9转换为n

Question

我在使用UDF的SO上发现了几个例子，但我试图在没有UDF的情况下做到这一点。

我在查询表格，抓取最新的@topCount记录数，将数字0至9转换为小写n，并显示唯一值。

它的工作原理，但我希望能得到一些更好的方式，如果存在的话。

如果UDF是答案，那么我会去争取那场斗争。

DECLARE @topCount AS INT; 
DECLARE @fromCharacter AS CHAR(1); 
DECLARE @toCharacter AS CHAR(1); 

SET @topCount = 2000; 
SET @toCharacter = 'n'; 

IF OBJECT_ID('tempdb..#temp_tbl') IS NOT NULL DROP TABLE #temp_tbl; 

SELECT TOP(@topCount) 
    LTRIM(RTRIM(CAST(msg_text AS VARCHAR(MAX)))) AS msgText  --msg_text is a datatype text 
    , msg_srce_text 
    , creat_gdat 
INTO #temp_tbl 
FROM dbo.appl_log 
ORDER BY 
    id DESC              --id is the PK 
; 

SET @fromCharacter = '0'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '1'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '2'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '3'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '4'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '5'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '6'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '7'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '8'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SET @fromCharacter = '9'; 
UPDATE #temp_tbl 
SET 
    msgText = REPLACE(msgText, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msgText) > 0; 
UPDATE #temp_tbl 
SET 
    msg_srce_text = REPLACE(msg_srce_text, @fromCharacter, @toCharacter) 
WHERE CHARINDEX(@fromCharacter, msg_srce_text) > 0; 

SELECT MIN(creat_gdat) AS fromDateTime, MAX(creat_gdat) AS toDateTime, @topCount AS mostRecent FROM #temp_tbl; 

SELECT 
    msgText 
    , msg_srce_text 
    , COUNT(*) AS count 
    , MAX(creat_gdat) AS mostRecentDateTime 
FROM #temp_tbl 
GROUP BY 
    msgText 
    , msg_srce_text 
ORDER BY 
    msgText DESC 
; 

GO 

Answer 1

使用REPLACE FUNCTION u能代替所有的数字data.Try这个..

UPDATE #temp_tbl 
SET msgtext = Replace (Replace (Replace (Replace (Replace (Replace (
       Replace (Replace (Replace (Replace (
       msgtext, '0', 'n'), '1', 'n'), '2', 'n'), '3', 'n'), '4', 'n'), 
       '5', 'n'), '6', 'n'), '7', 'n'), '8', 'n'), '9', 'n'), 
     msg_srce_text = Replace (Replace (Replace (Replace (Replace (Replace 
         (Replace (Replace (Replace (Replace 
         (msg_srce_text,'0', 'n'), '1', 'n'), '2', 'n'), '3', 'n'), '4', 'n'), 
       '5', 'n'), '6', 'n'), '7', 'n'), '8', 'n'), '9', 'n')