删除所有空格并将多行合并为 SQL 中的单行

本文介绍了删除所有空格并将多行合并为 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