SqlServer中去除字段空格及特殊空格/回车/换行等

 1.去除左边空格   LTRIM()

2.去除右边空格   RTRIM()

3. 去除回车符    REPLACE(@str, CHAR(13) , '') 

4.去除换行符     REPLACE(str, CHAR(10), '') 

5.去除回车换行符    REPLACE(str, CHAR(13) + CHAR(10), '')

6.替换空格   REPLACE(@str,' ','')

-- 定义字符串str
DECLARE @str nvarchar(100) ='" 100001",
" 10002 ","10003","10004 "'
DECLARE @sql varchar(max)
-- 替换字符串中的空格等特殊字符
SET @str=REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@str)),'"',''),' ',''),CHAR(13) + CHAR(10), '')

-- 将字符串拆分成列
set @sql='select col='''+ replace(LTRIM(RTRIM(@str)),',',''' union all select ''')+''''
PRINT @sql
exec (@sql)