SQL中巧用Translate和Replace配合替换字符串

时间:2019-09-09 10:30:01 来源:健康中国网 当前位置:品艺术英伦 > 学习 > 手机阅读

常用SQL的朋友应该知道,SQL中一般都会提供一些字符串处理函数。如果要您把所有的字符串处理函数都摆出来,估计您心里就没底了。但如果我问您replace函数,您肯定用过,这是批量替换字符串的常用函数。

常见数据库支持的字符串处理函数

我先不卖关子了,下面把所有最新版本SQLServer支持的字符串函数都列出来,其中画红框的两个就是今天我要讲的,您看看先有个底。

SQL中巧用Translate和Replace配合替换字符串

SQLServer的字符串函数跟MySQL、PostgreSQL比起来,确实是太少了。

下图是MySQL支持的常用字符串函数,当然也有replace函数,用法与SQLServer大同小异,您可以参考一下:

SQL中巧用Translate和Replace配合替换字符串

下图是PostgreSQL支持的常用字符串处理函数,当然也有replace函数,用法与SQLServer和MySQL也是大同小异,一并贴出来供您参考下:

SQL中巧用Translate和Replace配合替换字符串

通过上面三图对比我们大概可以看出,不同数据库系统支持的字符串处理函数都差不多吧,比如都支持trim、replace、left、right、substring等等,还有一些可能名称不同,内涵和用法却都很相似。

真是搞不懂微软为何如此吝啬,这么有实力的IT巨头,数据库支持的函数却这么贫瘠。一些我们很需要的函数,总是姗姗来迟。比如像分割字符串的STRING_SPLIT、批量替换的TRANSLATE、去掉两头空格的TRIM等,晚一点就晚一点吧,总比没有强。

批量替换,看似简单的大问题

说批量替换问题是小问题,是因为我们总会找到办法处理,配合循环和replace,总有办法解决,但有时候确实会很麻烦,比如,请问您如何把字符串

12@3#4$56^78*9!/@#$^*

中的非数字字符替换掉?

确实不难对吧,比如写个循环把数字抓出来或者把非数字剔除,还比如用replace一个一个替换。循环抓取的我就不写了,有兴趣的朋友可以看看我之前的文章中有过这样的例子,我们下面尝试用replace来实现:

declare @str varchar(100);
set @str='12@3#4$56^78*9!/@#$^*';
set @str=replace(@str,'@',space(0));
set @str=replace(@str,'#',space(0));
set @str=replace(@str,'$',space(0));
set @str=replace(@str,'^',space(0));
set @str=replace(@str,'*',space(0));
set @str=replace(@str,'!',space(0));
set @str=replace(@str,'/',space(0));

运行结果参考下图:

SQL中巧用Translate和Replace配合替换字符串

结果是没错的,实现了,估计大部分朋友首先想到的就是这么写。但您觉得爽吗?如果非数字字符还有很多呢?

真的很不爽,虽然能实现,但确实很麻烦。但如果用translate,就简单多了。下面我们就用神奇的Translate函数试试吧。

TRANSLATE:另类的批量却能解决大问题

SQLServer中有一个translate函数,估计您未必熟悉,因这是2017版才推出的函数。有朋友会问,既然有了replace,为何还要多此一举搞个translate出来呢?

其实translate与replace是大不相同的,我们先看看其语法定义:

TRANSLATE ( 字符串,待替换字符集,替换为字符集)

眼尖的朋友应该能看出来,translate函数中,待替换的内容是字符集合,而不是单纯的字符串。还以上面的例子为例。

12@3#4$56^78*9!/@#$^*

这里面的非数字字符,有@#$^*!/七个,我们可以批量将7个特殊字符替换掉。脚本如下:

select translate(@str,'@#$^*!/',space(7));

怎么样,是不是很简单,第一个参数是待替换的字符串,第二个参数是需要被替换的所有字符,第三个参数是各自替换成对应位置的对应字符。我们一起看看运行效果:

SQL中巧用Translate和Replace配合替换字符串

其中七个特殊字符,映射的是七个空格,稍显遗憾,中间的空格并没有被替换掉。这也是translate函数的一个要求,就是待替换字符个数与替换为字符个数必须要一样多。剩余的空格我们结合replace,一次就搞定了,脚本如下:

select replace(translate(@str,'@#$^*!/',space(7)),space(1),space(0));

怎么样,是不是很神奇?

学习本月排行

学习精选