T-SQL에서 중복 공백을 단일 공백으로 바꾸기
주어진 필드에 문자 사이에 두 개 이상의 공백이 없는지 확인해야합니다 (모든 공백은 고려하지 않고 공백 만 포함).
그래서
'single spaces only'
로 바뀌어야한다
'single spaces only'
아래는 작동하지 않습니다
select replace('single spaces only',' ',' ')
결과적으로
'single spaces only'
저는 CLR 기반 솔루션보다 네이티브 T-SQL을 고수하는 것을 선호합니다.
생각?
더 깔끔하게 :
select string = replace(replace(replace(' select single spaces',' ','<>'),'><',''),'<>',' ')
산출:
단일 공간 선택
이것은 작동합니다.
declare @test varchar(100)
set @test = 'this is a test'
while charindex(' ',@test ) > 0
begin
set @test = replace(@test, ' ', ' ')
end
select @test
한 행에 특정 수 이상의 공백이 없다는 것을 알고 있다면 바꾸기를 중첩 할 수 있습니다.
replace(replace(replace(replace(myText,' ',' '),' ',' '),' ',' '),' ',' ')
4 개의 교체는 최대 16 개의 연속 공백을 수정해야합니다 (16, 8, 4, 2, 1).
훨씬 더 길 수 있다면 인라인 함수와 같은 작업을 수행해야합니다.
CREATE FUNCTION strip_spaces(@str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
WHILE CHARINDEX(' ', @str) > 0
SET @str = REPLACE(@str, ' ', ' ')
RETURN @str
END
그럼 그냥
SELECT dbo.strip_spaces(myText) FROM myTable
update mytable
set myfield = replace (myfield, ' ', ' ')
where charindex(' ', myfield) > 0
바꾸기는 모든 이중 공백에서 작동하므로 여러 번 바꿀 필요가 없습니다. 이것은 세트 기반 솔루션입니다.
This is somewhat brute force, but will work
CREATE FUNCTION stripDoubleSpaces(@prmSource varchar(max)) Returns varchar(max)
AS
BEGIN
WHILE (PATINDEX('% %', @prmSource)>0)
BEGIN
SET @prmSource = replace(@prmSource ,' ',' ')
END
RETURN @prmSource
END
GO
-- Unit test --
PRINT dbo.stripDoubleSpaces('single spaces only')
single spaces only
It can be done recursively via the function:
CREATE FUNCTION dbo.RemSpaceFromStr(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
BEGIN
RETURN (CASE WHEN CHARINDEX(' ', @str) > 0 THEN
dbo.RemSpaceFromStr(REPLACE(@str, ' ', ' ')) ELSE @str END);
END
then, for example:
SELECT dbo.RemSpaceFromStr('some string with many spaces') AS NewStr
returns:
NewStr
some string with many spaces
Or the solution based on method described by @agdk26 or @Neil Knight (but safer)
both examples return output above:
SELECT REPLACE(REPLACE(REPLACE('some string with many spaces'
, ' ', ' ' + CHAR(7)), CHAR(7) + ' ', ''), ' ' + CHAR(7), ' ') AS NewStr
--but it remove CHAR(7) (Bell) from string if exists...
or
SELECT REPLACE(REPLACE(REPLACE('some string with many spaces'
, ' ', ' ' + CHAR(7) + CHAR(7)), CHAR(7) + CHAR(7) + ' ', ''), ' ' + CHAR(7) + CHAR(7), ' ') AS NewStr
--but it remove CHAR(7) + CHAR(7) from string
Caution:
Char/string used to replace spaces shouldn't exist on begin or end of string and stand alone.
Here is a simple function I created for cleaning any spaces before or after, and multiple spaces within a string. It gracefully handles up to about 108 spaces in a single stretch and as many blocks as there are in the string. You can increase that by factors of 8 by adding additional lines with larger chunks of spaces if you need to. It seems to perform quickly and has not caused any problems in spite of it's generalized use in a large application.
CREATE FUNCTION [dbo].[fnReplaceMultipleSpaces] (@StrVal AS VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
SET @StrVal = Ltrim(@StrVal)
SET @StrVal = Rtrim(@StrVal)
SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 16 spaces
SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 8 spaces
SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 4 spaces
SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 2 spaces
SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 2 spaces (for odd leftovers)
RETURN @StrVal
END
Found this while digging for an answer:
SELECT REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM('1 2 3 4 5 6'))
,' ',' '+CHAR(7))
,CHAR(7)+' ','')
,CHAR(7),'') AS CleanString
where charindex(' ', '1 2 3 4 5 6') > 0
The full answer (with explanation) was pulled from: http://techtipsbysatish.blogspot.com/2010/08/sql-server-replace-multiple-spaces-with.html
On second look, seems to be just a slightly different version of the selected answer.
This is the solution via multiple replace, which works for any strings (does not need special characters, which are not part of the string).
declare @value varchar(max)
declare @result varchar(max)
set @value = 'alpha beta gamma delta xyz'
set @result = replace(replace(replace(replace(replace(replace(replace(
@value,'a','ac'),'x','ab'),' ',' x'),'x ',''),'x',''),'ab','x'),'ac','a')
select @result -- 'alpha beta gamma delta xyz'
Method #1
The first method is to replace extra spaces between words with an uncommon symbol combination as a temporary marker. Then you can replace the temporary marker symbols using the replace function rather than a loop.
Here is a code example that replaces text within a String variable.
DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!';
SELECT REPLACE(REPLACE(REPLACE(@testString, ' ', '*^'), '^*', ''), '*^', ' ');
Execution Time Test #1: In ten runs of this replacement method, the average wait time on server replies was 1.7 milliseconds and total execution time was 4.6 milliseconds. Execution Time Test #2: The average wait time on server replies was 1.7 milliseconds and total execution time was 3.7 milliseconds.
Method #2
The second method is not quite as elegant as the first, but also gets the job done. This method works by nesting four (or optionally more) replace statements that replace two blank spaces with one blank space.
DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!';
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@testString,' ',' '),' ',' '),' ',' '),' ',' ')
Execution Time Test #1: In ten runs of this replacement method, the average wait time on server replies was 1.9 milliseconds and total execution time was 3.8 milliseconds. Execution Time Test #2: The average wait time on server replies was 1.8 milliseconds and total execution time was 4.8 milliseconds.
Method #3
The third method of replacing extra spaces between words is to use a simple loop. You can do a check on extra spaces in a while loop and then use the replace function to reduce the extra spaces with each iteration of the loop.
DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!';
WHILE CHARINDEX(' ',@testString) > 0
SET @testString = REPLACE(@testString, ' ', ' ')
SELECT @testString
Execution Time Test #1: In ten runs of this replacement method, the average wait time on server replies was 1.8 milliseconds and total execution time was 3.4 milliseconds. Execution Time Test #2: The average wait time on server replies was 1.9 milliseconds and total execution time was 2.8 milliseconds.
I use FOR XML PATH solution to replace multiple spaces into single space
The idea is to replace spaces with XML tags Then split XML string into string fragments without XML tags Finally concatenating those string values by adding single space characters between two
Here is how final UDF function can be called
select dbo.ReplaceMultipleSpaces(' Sample text with multiple space ')
DECLARE @str varchar(150)
SET @str='Hello My name is Jiyaul mustafa'
Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')
I typically use this approach:
declare @s varchar(50)
set @s = 'TEST TEST'
select REPLACE(REPLACE(REPLACE(@s,' ','[o][c]'),'[c][o]',''),'[o][c]',' ')
You can try this:
select Regexp_Replace('single spaces only','( ){2,}', ' ') from dual;
update mytable
set myfield = replace(myfield, ' ', ' ')
where myfield like '% %'
Try this..
참고URL : https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql
'Programing' 카테고리의 다른 글
파이썬에 // 연산자에 해당하는 상한선이 있습니까? (0) | 2020.08.27 |
---|---|
TabBar 이미지의 크기는 얼마입니까? (0) | 2020.08.27 |
문자가 빈 공간과 같은지 확인하는 방법은 무엇입니까? (0) | 2020.08.27 |
소수점 이하 자리를 두 배로 이동 (0) | 2020.08.27 |
Angular2에서 텍스트를 자르는 방법은 무엇입니까? (0) | 2020.08.27 |