如何用REPLACE与SUBSTRING函数去除Col1中::之后的内容？

给定包含各类数据定义、数值及函数的Col1字段，需求为提取::及其之后内容之前的部分；若字段中无::，则保留原内容。原SQL处理时出现数值截断、函数内容错误替换的问题，以下是修正方案：

原SQL的问题分析 #

1. 当字段不含::时，CHARINDEX('::', Col1)返回0，导致SUBSTRING取整个字段内容，后续REPLACE将其替换为空，引发错误结果。
2. 多余的REPLACE('(''','''')逻辑错误破坏了无::的函数字符串（如getdate()）。

正确SQL实现 #

适用于SQL Server的写法 #

SELECT 
    Col1,
    CASE 
        WHEN CHARINDEX('::', Col1) > 0 THEN LEFT(Col1, CHARINDEX('::', Col1) - 1)
        ELSE Col1
    END AS Col2
FROM tbl_string_pattern;

适用于PostgreSQL的写法 #

SELECT 
    Col1,
    CASE 
        WHEN STRPOS(Col1, '::') > 0 THEN LEFT(Col1, STRPOS(Col1, '::') - 1)
        ELSE Col1
    END AS Col2
FROM tbl_string_pattern;

逻辑说明 #

• 通过CASE WHEN判断字段中是否存在::：
  • 存在时，用LEFT函数截取::出现位置之前的所有字符；
  • 不存在时，直接返回原字段内容，避免无意义的替换操作。

验证结果 #

执行上述SQL后，将得到预期输出：

Col1                                        Col2
------------------------------------------------------------------------
'-'::"varchar" COLLATE "default"            '-'
'-1'::integer                               '-1'
'0'::smallint                               '0'
'1'::"varchar" COLLATE "default"            '1'
(get_val())::"timestamp"                    (get_val())
0                                           0
0.0                                         0.0
10                                          10
210                                         210
90000                                       90000
8                                           8
getdate()                                   getdate()

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