August 6, 2015 at 2:36 am
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And i need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
any Ideas?
Many thanks
August 6, 2015 at 2:58 am
Edward-445599 (8/6/2015)
I have the following scenario, The contents of main file are like :ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And i need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
any Ideas?
Many thanks
SELECT [a].[ORIGINAL_VALUE],
[a].[EXPECTED_VALUE],
REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(ORIGINAL_VALUE)), '||',
'|' + CHAR(7)), CHAR(7) + '|', ''),
CHAR(7), ''), '|', '=') AS CleanString
FROM ( VALUES
( 'ServerCentral|||||forum|||||||||||||||is||||||the||best', 'ServerCentral=forum=is=the=best'),
( 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it', 'so=be=on=it') ) a ( ORIGINAL_VALUE, EXPECTED_VALUE );
Results: -
ORIGINAL_VALUE EXPECTED_VALUE CleanString
--------------------------------------------------------------- ------------------------------- ---------------------------------
ServerCentral|||||forum|||||||||||||||is||||||the||best ServerCentral=forum=is=the=best ServerCentral=forum=is=the=best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it so=be=on=it so=be=on=it
See REPLACE Multiple Spaces with One article, by Jeff Moden[/url].
August 6, 2015 at 5:33 am
Hi,
Try this.. Hope it will work for you:
WITH CTE(col) AS
(
SELECT
'ServerCentral|||||forum|||||||||||||||is||||||the||best'
UNION ALL
SELECT 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it'
)
SELECT
REPLACE (
REPLACE (
REPLACE(
REPLACE(col,'||','|' + '=')
,'=' +'|','')
,'=','')
,'|','=')
FROM CTE
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 6, 2015 at 7:25 am
You only need three replaces.
WITH CTE(col) AS
(
SELECT
'ServerCentral|||||forum|||||||||||||||is||||||the||best'
UNION
SELECT 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it'
UNION
SELECT 'An||||additional|test||with|occurences||||of|a|||||single|||||pipe.'
)
SELECT REPLACE(REPLACE(REPLACE(col, '|', '|='), '=|', ''), '|', '')
FROM CTE
The previous solutions treat an odd number of pipes and an even number of pipes differently, because the initial replace only looks for double pipes. This is not an issue when replacing multiple occurrences of a character with that same character as in Jeff's article on replacing multiple spaces with a single space.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 6, 2015 at 9:01 am
drew.allen (8/6/2015)
You only need three replaces.
WITH CTE(col) AS
(
SELECT
'ServerCentral|||||forum|||||||||||||||is||||||the||best'
UNION
SELECT 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it'
UNION
SELECT 'An||||additional|test||with|occurences||||of|a|||||single|||||pipe.'
)
SELECT REPLACE(REPLACE(REPLACE(col, '|', '|='), '=|', ''), '|', '')
FROM CTE
The previous solutions treat an odd number of pipes and an even number of pipes differently, because the initial replace only looks for double pipes. This is not an issue when replacing multiple occurrences of a character with that same character as in Jeff's article on replacing multiple spaces with a single space.
Drew
Nice Solution! Will have to hang on to that one.
August 6, 2015 at 10:00 am
This reminded me of an article that explains the method used here. However, the discussion of the article showed a faster option. This is the article: http://www.sqlservercentral.com/articles/T-SQL/68378/
And this is an adaptation to the faster solution for your problem.
SELECT [a].[ORIGINAL_VALUE],
[a].[EXPECTED_VALUE],
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ORIGINAL_VALUE COLLATE LATIN1_GENERAL_BIN,
'|||||||||||||||||||||||||||||||||','|'),
'|||||||||||||||||','|'),
'|||||||||','|'),
'|||||','|'),
'|||','|'),
'||','|'),
'||','|'), '|', '=') AS CleanString
FROM ( VALUES
( 'ServerCentral|||||forum|||||||||||||||is||||||the||best', 'ServerCentral=forum=is=the=best'),
( 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it', 'so=be=on=it')) a (ORIGINAL_VALUE, EXPECTED_VALUE);
Of course, you can always create an inline table valued function to reduce all that code.
IF OBJECT_ID('dbo.ReplaceBarsWithEqualSign') IS NOT NULL DROP FUNCTION dbo.ReplaceBarsWithEqualSign
GO
CREATE FUNCTION dbo.ReplaceBarsWithEqualSign(
@String varchar(8000)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String COLLATE LATIN1_GENERAL_BIN,
'|||||||||||||||||||||||||||||||||','|'),
'|||||||||||||||||','|'),
'|||||||||','|'),
'|||||','|'),
'|||','|'),
'||','|'),
'||','|'), '|', '=') AS CleanString
GO
SELECT [a].[ORIGINAL_VALUE],
[a].[EXPECTED_VALUE],
r.CleanString
FROM ( VALUES
( 'ServerCentral|||||forum|||||||||||||||is||||||the||best', 'ServerCentral=forum=is=the=best'),
( 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it', 'so=be=on=it')) a (ORIGINAL_VALUE, EXPECTED_VALUE)
CROSS APPLY dbo.ReplaceBarsWithEqualSign(ORIGINAL_VALUE) r;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply