在Oracle中的字符串中修剪空白(换行和制表符空间)

问题描述

我需要在Oracle查询中修剪换行符(从字符串的开头和结尾开始的Chr(13)和Chr(10)和制表符空间).我了解到，在Oracle中没有简单的方法可以修剪多个字符. 修剪"功能仅修剪单个字符.如果我使用一个函数在循环中递归调用trim函数，将会降低性能.我听说regexp_replace可以匹配空格并将其删除.您能否指导一种可靠的方法来使用regexp_replace在String的开头和结尾修剪多个制表符或换行或它们的组合.如果还有其他方法，请指导我.

I need to trim New Line (Chr(13) and Chr(10) and Tab space from the beginning and end of a String) in an Oracle query. I learnt that there is no easy way to trim multiple characters in Oracle. "trim" function trims only single character. It would be a performance degradation if i call trim function recursivelly in a loop using a function. I heard regexp_replace can match the whitespaces and remove them. Can you guide of a reliable way to use regexp_replace to trim multiple tabspaces or new lines or combinations of them in beginning and end of a String. If there is any other way, Please guide me.

推荐答案

快速而肮脏的翻译功能如何?

How about the quick and dirty translate function?

这将删除string1中每个字符的所有出现:

This will remove all occurrences of each character in string1:

SELECT translate(
           translate(
               translate(string1, CHR(10), '')
           , CHR(13), '')
       , CHR(09), '') as massaged
FROM BLAH;

Regexp_replace是一个选项，但是根据表达式的复杂程度，您可能会看到性能下降.

Regexp_replace is an option, but you may see a performance hit depending on how complex your expression is.

这篇关于在Oracle中的字符串中修剪空白(换行和制表符空间)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！