Oracle转Firebird：TRANSLATE函数替换方案技术求助

我是SQL自学者，日常工作使用Oracle和Firebird数据库编写查询并优化效率。此前在Oracle中通过TRANSLATE函数完成spqr表kodqr列的字符转换查询，运行正常：

SELECT t.nazwa, s.nrkln, s.datsp, s.ilosp, k.seria
FROM spqr q, sprz s, towr t, kzak k
WHERE TRANSLATE(kodqr,
    '76543210?>FEDCBA@ONMLKJIHWVUTSRQP_^]ZYX89',
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ]^_?>'
) like '%'||:teks1||'%'
and s.id=q.idsprz and s.idfirm=q.idfirm
and t.idfirm = s.idfirm and t.id = s.idtowr
and s.idtowr = k.idtowr and s.idfirm = k.idfirm and s.idkzak=k.id

现需在Firebird中实现相同功能，尝试嵌套REPLACE函数时存在字符转换不全问题，且子查询返回多行报错（需加min才能运行），使用CASE函数方案也未成功，恳请提供可行解决方案。我试过的REPLACE代码如下：

SELECT t.nazwa, s.nrkln, s.datsp, s.ilosp, k.seria
FROM spqr q
JOIN sprz s ON (s.id = q.idsprz AND s.idfirm = q.idfirm)
JOIN towr t ON (t.id = s.idtowr AND t.idfirm = s.idfirm)
JOIN kzak k ON (s.idtowr = k.idtowr AND s.idfirm = k.idfirm AND s.idkzak = k.id)
WHERE
((SELECT min(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(
    kodqr,'7','0'),'6','1'),'5', '2'), '4', '3'), '3', '4'), '2', '5'),
    '1','6'),'0','7'), '8', '?'), '9','>'),
    'A', 'F'), 'B', 'E'), 'C', 'D'), 'D', 'C'), 'E', 'B'), 'F', 'A'),
     'G', '@'), 'H', 'O'), 'I', 'N'), 'J','M'),
    'K', 'L'), 'L', 'K'), 'M', 'J'), 'N', 'I'), 'O', 'H'), 'U', 'R'),
     'P', 'W'), 'R', 'U'), 'S', 'T'), 'T','S'),
    'Q', 'V'), 'W', 'P'), 'X', '_'), 'Y', '^'), 'Z', ']'), 'V', 'Q'),'S','T'),
    '>','9'),'_','X'),'?','8'),'@','G'),'^','Y'),']','Z'),'7','0'),'',''),
    '6','1'),'2','5'),'0','7')) from spqr)) like '%'||:teks1||'%'
GROUP BY t.nazwa, s.nrkln, s.datsp, s.ilosp, k.seria

可行解决方案：自定义TRANSLATE函数 #

Firebird无内置TRANSLATE函数，但可通过创建自定义函数模拟Oracle的TRANSLATE逻辑，避免嵌套REPLACE的顺序冲突和转换不全问题（Firebird 2.0及以上版本支持）。

1. 创建自定义TRANSLATE函数

CREATE OR ALTER FUNCTION TRANSLATE(p_input VARCHAR(1000), p_from_chars VARCHAR(100), p_to_chars VARCHAR(100))
RETURNS VARCHAR(1000)
AS
DECLARE VARIABLE v_result VARCHAR(1000);
DECLARE VARIABLE v_char CHAR(1);
DECLARE VARIABLE v_pos INTEGER;
BEGIN
  v_result = '';
  IF (p_input IS NULL OR p_from_chars IS NULL OR p_to_chars IS NULL) THEN
    RETURN NULL;
  FOR SELECT SUBSTRING(p_input FROM i FOR 1)
      FROM (SELECT ROW_NUMBER() OVER() AS i
            FROM rdb$types
            WHERE i <= CHAR_LENGTH(p_input)) AS nums
  INTO v_char DO
  BEGIN
    v_pos = POSITION(v_char IN p_from_chars);
    IF (v_pos > 0) THEN
    BEGIN
      IF (v_pos <= CHAR_LENGTH(p_to_chars)) THEN
        v_result = v_result || SUBSTRING(p_to_chars FROM v_pos FOR 1);
      ELSE
        v_result = v_result || ''; -- 对应Oracle中from_chars长于to_chars时，多余字符被删除
    END
    ELSE
      v_result = v_result || v_char; -- 不在from_chars中的字符保留原样
  END
  RETURN v_result;
END

2. 使用自定义函数改写查询

直接替换原Oracle查询中的TRANSLATE调用，同时采用标准JOIN语法：

SELECT t.nazwa, s.nrkln, s.datsp, s.ilosp, k.seria
FROM spqr q
JOIN sprz s ON s.id = q.idsprz AND s.idfirm = q.idfirm
JOIN towr t ON t.idfirm = s.idfirm AND t.id = s.idtowr
JOIN kzak k ON s.idtowr = k.idtowr AND s.idfirm = k.idfirm AND s.idkzak = k.id
WHERE TRANSLATE(q.kodqr,
    '76543210?>FEDCBA@ONMLKJIHWVUTSRQP_^]ZYX89',
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ]^_?>'
) LIKE '%' || :teks1 || '%'

原REPLACE方案问题分析 #

• 转换顺序冲突：嵌套REPLACE按顺序执行，比如先把'7'换成'0'，后续又将'0'换成'7'，导致已转换的字符被反向替换，出现转换不全。
• 子查询逻辑错误：SELECT min(...) from spqr会取整个spqr表转换结果的最小值，而非当前行的kodqr转换结果，完全不符合需求，必须去掉该子查询，直接对当前行的q.kodqr做转换。

内容的提问来源于stack exchange，提问作者Tuathe