반응형
/****************************************************************************************************************
-- Title : [SQL2012] Data extract from string which is delimited by non-fixed(several) spaces.
-- Reference : stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql
-- Key word : sql server space pivot crosstab 멀티값 싱글값 스페이스 unpivot delimiter 구분자
****************************************************************************************************************/
-- using CHAR() code if there are '<' or '>' in the string.
-- Title : [SQL2012] Data extract from string which is delimited by non-fixed(several) spaces.
-- Reference : stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql
-- Key word : sql server space pivot crosstab 멀티값 싱글값 스페이스 unpivot delimiter 구분자
****************************************************************************************************************/
-- using '<' and '>'
declare @txt nvarchar(1000);
declare @txt nvarchar(1000);
set @txt = 'abc a b d';
select string = replace( replace( replace( @txt
, ' '
,'<>'
)
, '><'
, ''
)
, '<>'
,' '
);
-- using CHAR() code if there are '<' or '>' in the string.
declare @txt nvarchar(1000);
set @txt = 'release < now > abc def';
select string = replace( replace( replace( @txt
, ' '
, CHAR(17) + CHAR(18)
)
, CHAR(18) + CHAR(17)
, ''
)
, CHAR(17) + CHAR(18)
, ' '
반응형