删除所有空格并将多行合并为 SQL 中的单行 [英] Remove all spaces and combine multiple lines to single line in SQL

问题描述

从 SQL Server 2014 中的字符串中删除所有空格的最佳方法是什么?

What is the best way to remove all spaces from a string in SQL Server 2014?

我的字符串是:

Maximize your productivity for building engaging,

 beautiful web mapping applications

尝试删除字符串之间的 Enter 和 Tab 空格以及单词之间的 1 个空格.结果应该是这样的:

Trying to remove enter and tab spaces between string and 1 space between words. Result should be like:

Maximize your productivity for building engaging, beautiful web mapping applications

推荐答案

如果对 UDF 开放，以下将删除所有控制字符和重复空格.

If open to a UDF, the following will remove all control characters and repeating spaces.

删除重复空格的灵感来自于几个月前 Gordon 的回答(OK 被盗).

The removal of the repeating spaces was inspired (OK stolen) from Gordon's answer a few months ago.

示例

Declare @S varchar(max) = 'Maximize your productivity for building engaging,

 beautiful web mapping applications'


Select [dbo].[svf-Str-Strip-Control](@S)

退货

Maximize your productivity for building engaging, beautiful web mapping applications

感兴趣的 UDF

CREATE FUNCTION [dbo].[svf-Str-Strip-Control](@S varchar(max))
Returns varchar(max)
Begin
    Select @S=Replace(@S,char(n),' ')
     From  (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31) ) N(n)

    Return LTrim(RTrim(Replace(Replace(Replace(@S,' ','><'),'<>',''),'><',' ')))
End
--Select [dbo].[svf-Str-Strip-Control]('Michael        '+char(13)+char(10)+'LastName')  --Returns: Michael LastName

这篇关于删除所有空格并将多行合并为 SQL 中的单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！