【Daily】SQL中替换字符串中连续空格为一个空格的方法
SQL中替换字符串中连续空格为一个空格的方法:
http://www.sqlservercentral.com/articles/T-SQL/68378/
O
OO
OOO
OOOO
OOOOO
OOOOOO
OOOOOOO
OOOOOOOO
第一步:把OO替换为OX
O
OX
OXO
OXOX
OXOXO
OXOXOX
OXOXOXO
OXOXOXOX
第二步:把XO替换为空
O
OX
O
OX
O
OX
O
OX
第三步:把X替换为空
O
O
O
O
O
O
O
O
DECLARE @Demo TABLE(OriginalString VARCHAR(8000))
INSERT INTO @Demo (OriginalString)
SELECT ' This has multiple unknown spaces in it. ' UNION ALL
SELECT 'So does this!' UNION ALL
SELECT 'As does this' UNION ALL
SELECT 'This, that, and the other thing.' UNION ALL
SELECT 'This needs no repair.'
--===== Reduce each group of multiple spaces to a single space
-- for a whole table without functions, loops, or other
-- forms of slow RBAR. In the following example, CHAR(7)
-- is the "unlikely" character that "X" was used for in
-- the explanation.
SELECT REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(OriginalString))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'') AS CleanString --Changes the remaining X's to nothing
FROM @Demo
WHERE CHARINDEX(' ',OriginalString) > 0
http://www.sqlservercentral.com/articles/T-SQL/68378/
O
OO
OOO
OOOO
OOOOO
OOOOOO
OOOOOOO
OOOOOOOO
第一步:把OO替换为OX
O
OX
OXO
OXOX
OXOXO
OXOXOX
OXOXOXO
OXOXOXOX
第二步:把XO替换为空
O
OX
O
OX
O
OX
O
OX
第三步:把X替换为空
O
O
O
O
O
O
O
O
DECLARE @Demo TABLE(OriginalString VARCHAR(8000))
INSERT INTO @Demo (OriginalString)
SELECT ' This has multiple unknown spaces in it. ' UNION ALL
SELECT 'So does this!' UNION ALL
SELECT 'As does this' UNION ALL
SELECT 'This, that, and the other thing.' UNION ALL
SELECT 'This needs no repair.'
--===== Reduce each group of multiple spaces to a single space
-- for a whole table without functions, loops, or other
-- forms of slow RBAR. In the following example, CHAR(7)
-- is the "unlikely" character that "X" was used for in
-- the explanation.
SELECT REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(OriginalString))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'') AS CleanString --Changes the remaining X's to nothing
FROM @Demo
WHERE CHARINDEX(' ',OriginalString) > 0