SQL字符串替换：将查询结果中数字'2'替换为'factura'的方法

嘿，这个需求很好实现！你可以用SQL里的CASE表达式或者REPLACE函数来搞定，我给你两种具体方案，你可以根据实际情况选：

方案一：用CASE表达式（推荐，精准匹配单一值） #

如果Cust.[Document Type]只有值为'2'时需要替换成'factura'，其他值保持原样，CASE表达式是最稳妥的选择。修改后的查询语句如下：

SELECT 
  Detail.[Entry No_], 
  'Cliente' AS Tipo, 
  Cust.[Customer No_] AS Cliente, 
  Detail.[Posting Date] AS DATA, 
  Detail.[Document No_] AS Documento, 
  Detail.[Amount (LCY)] AS Valor, 
  -- 核心替换逻辑
  CASE 
    WHEN Cust.[Document Type] = '2' THEN 'factura'
    ELSE Cust.[Document Type]
  END AS LiqPorTipo, 
  Cust.[Document No_] AS LiqPorNDocumento, 
  '97' AS Conta, 
  'MR' AS Loja, 
  'SUPER' AS Utilizador, 
  'MR01' AS [你的列名] -- 记得补全原语句里的列名哦
-- 实际使用时别忘了补上FROM和JOIN关联条件
FROM [明细表明] Detail
JOIN [客户表明] Cust ON Detail.[关联字段] = Cust.[关联字段]

这种方法的好处是灵活，以后要是需要替换其他值，直接加WHEN条件就行。

方案二：用REPLACE函数（替换所有出现的特定字符串） #

如果Cust.[Document Type]里只要出现'2'就需要替换（比如值是'22'会变成'facturafactura'），可以用REPLACE函数：

SELECT 
  Detail.[Entry No_], 
  'Cliente' AS Tipo, 
  Cust.[Customer No_] AS Cliente, 
  Detail.[Posting Date] AS DATA, 
  Detail.[Document No_] AS Documento, 
  Detail.[Amount (LCY)] AS Valor, 
  -- 替换逻辑
  REPLACE(Cust.[Document Type], '2', 'factura') AS LiqPorTipo, 
  Cust.[Document No_] AS LiqPorNDocumento, 
  '97' AS Conta, 
  'MR' AS Loja, 
  'SUPER' AS Utilizador, 
  'MR01' AS [你的列名]
FROM [明细表明] Detail
JOIN [客户表明] Cust ON Detail.[关联字段] = Cust.[关联字段]

注意：如果Document Type是数字类型，得先转成字符串再用REPLACE，比如REPLACE(CAST(Cust.[Document Type] AS VARCHAR(10)), '2', 'factura')。

内容的提问来源于stack exchange，提问作者Cátia Matos