Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««3738394041

REPLACE Multiple Spaces with One Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 8:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 7:52 PM
Points: 2, Visits: 6
how about this ?
select '|'+ rtrim(ltrim(replace(replace(replace(replace('   Homer               Simpson  is   kewl ','  ','_'),'__',''),'_ ',' '),'_',' ')))   +'|'

its a good thing we only have odd and even number... ;o)
Post #1410876
Posted Monday, January 28, 2013 1:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 537, Visits: 2,105
This approach was published in a solution quite early in this discussion.
Post #1412232
Posted Wednesday, May 22, 2013 1:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:33 AM
Points: 1,821, Visits: 2,173
Stumbled across this thread with a Google search. (Amazing how all paths lead 'home'.)

The elegance of the solution to this problem in SQL is amazing. Got a warm glow when I contemplated it's beauty. That quickly vanished as I smacked myself for not having figured it out on my own.


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1455682
Posted Saturday, May 25, 2013 1:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 37,098, Visits: 31,648
Thomas Abraham (5/22/2013)
Stumbled across this thread with a Google search. (Amazing how all paths lead 'home'.)

The elegance of the solution to this problem in SQL is amazing. Got a warm glow when I contemplated it's beauty. That quickly vanished as I smacked myself for not having figured it out on my own.


Thak you very much for the feedback and for stopping by. I hope you're talking about the "Nested Replace" solution and not the one in the article. As happens many times, the discussion led to a solution better than the one in the article. The introduction in the article has a link to the "Nested Replace" soultion.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1456809
Posted Sunday, May 26, 2013 1:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
I'm late to this party, but here's my entirely TSQL offering to remove duplicate spaces. Most of the code below has been not so shamelessly borrowed from Jeff Moden's work, especially the famous DelimitedSplit8K function which I have found to be one of the most useful tools in the toolbox.

I have no idea how this compares to the other solutions but I thought I'd just throw it into the mix. It's based mostly off of some other variations I've developed from the DelimitedSplit8K function and I think it takes a different approach to the problem. Basically, it splits the string and then puts it back together again. Whether that's better or not I'll let the scorekeeper determine.


/* Complete functions for creating a test set are listed here below the main function */

/* Create some test data (run once) */
DROP TABLE dbo.AlphaNumericWithSpaces
SELECT * INTO dbo.AlphaNumericWithSpaces
FROM [dbo].[CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces](1000,50,8,20)

/* Run just to see what the test data looks like */
SELECT * FROM dbo.AlphaNumericWithSpaces AS s



/* Since this is an itvf you can use cross apply */

SELECT
CleanString
FROM
dbo.AlphaNumericWithSpaces AS s
CROSS APPLY
dbo.itvfRemoveDuplicateSpaces(s.CSV)


The functions are below:


/* This is the actual duplicate removal function */

CREATE FUNCTION [dbo].[itvfRemoveDuplicateSpaces]
(
@pInputString VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pInputString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pInputString,t.N,1) = ' ' OR t.N = 0)
),
cteItem(Item) AS
(
SELECT Item = SUBSTRING(@pInputString,s.N1,ISNULL(NULLIF(CHARINDEX(' ',@pInputString,s.N1),0)-s.N1,8000))
FROM cteStart s
)
SELECT
STUFF(
(SELECT
COALESCE(NULLIF(' ' + i.Item,' '),NULL) AS [text()]
FROM
cteItem i
FOR XML PATH('')),1,1,'')
AS CleanString
;
GO


  

/* Create the test data function */
/* NOTE: This is the correct function to use for this example. Thanks to Michael Meierruth for spotting the error. */

CREATE FUNCTION [dbo].[CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces]
(
@pNumberOfRows INT,
@pNumberOfElementsPerRow INT,
@pMinElementwidth INT,
@pMaxElementWidth INT
)
RETURNS TABLE
AS
RETURN

SELECT TOP (@pNumberOfRows) --Controls the number of rows in the test table
ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
CSV =
(--==== This creates each CSV
SELECT CAST(
STUFF(
(
SELECT TOP (@pNumberOfElementsPerRow) --Controls the number of CSV elements in each row
' '
+ dbo.svfGenerateUniqueCode(@pMinElementwidth,@pMaxElementWidth,' ')
+ '|'
+ dbo.svfGenerateUniqueCode(@pMinElementwidth,@pMaxElementWidth,' ')
+ REPLICATE(' ',(ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))/10000000)/10)

FROM sys.All_Columns ac3 --Classic cross join pseudo-cursor
CROSS JOIN sys.All_Columns ac4 --can produce row sets up 16 million.
WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.
FOR XML PATH('')
)
,1,1,'')
AS VARCHAR(8000))
)
FROM sys.All_Columns ac1 --Classic cross join pseudo-cursor
CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows

GO



/* Used by the test data function to generate random values */

CREATE FUNCTION [dbo].[svfGenerateUniqueCode]
(
@CodeMinLength INT
,@CodeMaxLength INT
,@SpecialChar VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Code VARCHAR(100)

DECLARE @CodeData TABLE
(
CodeChar VARCHAR(1)
)

DECLARE @Num TABLE
(
Digit INT NOT NULL
PRIMARY KEY CLUSTERED
)

IF @CodeMaxLength <= @CodeMinLength
SET @CodeMaxLength = @CodeMinLength + 1


INSERT INTO @Num
(
Digit
)
SELECT
Digit = 0
UNION ALL
SELECT
Digit = 1
UNION ALL
SELECT
Digit = 2
UNION ALL
SELECT
Digit = 3
UNION ALL
SELECT
Digit = 4
UNION ALL
SELECT
Digit = 5
UNION ALL
SELECT
Digit = 6
UNION ALL
SELECT
Digit = 7
UNION ALL
SELECT
Digit = 8
UNION ALL
SELECT
Digit = 9
ORDER BY
1

INSERT INTO @CodeData
(
CodeChar
)
SELECT
CodeChar = SUBSTRING(b.Characters,a.RAND_INT % b.MOD,1)
FROM
(
SELECT
aa.Number
,RAND_INT = ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))
FROM
(
SELECT Number = a.Digit + (b.Digit * 10) FROM @Num a CROSS JOIN @Num b
) aa
) a
CROSS JOIN
(
SELECT
MOD = LEN(bb.Characters) - 1
,bb.Characters
FROM
(
SELECT
Characters =
'ABCDEFGHJKLMNPQURSUVWXYZ'
+ 'abcdefghjkmnpqursuvwxyz'
+ '0123456789'
+ @SpecialChar
) bb
) b
ORDER BY
(SELECT MyNewID FROM dbo.iFunction)

SELECT
@Code = ''

SELECT
@Code = @Code + CodeChar
FROM
@CodeData

SELECT
@Code =
-- Random length from MIN to MAX Characters
SUBSTRING(@Code,1,@CodeMinLength + (ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))) % (@CodeMaxLength - @CodeMinLength + 1))

SET @Code = NULLIF(LTRIM(RTRIM(@Code)),'')

RETURN @Code

END
GO



CREATE VIEW [dbo].[iFunction] AS
/**********************************************************************************************************************
Purpose:
This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
a thing directly in the function. This view also solves the same problem for GETDATE().

Usage:
SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
SELECT MyDate FROM dbo.iFunction; --Returns a Date

Revision History:
Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code. No logic changes.
**********************************************************************************************************************/

SELECT MyNewID = NEWID(),
MyDate = GETDATE();

GO


 
Post #1456844
Posted Sunday, May 26, 2013 5:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 537, Visits: 2,105
Where/what is this iFunction?
What's the difference between CreateCsv8K2DimArrayRandomAlphaNumeric and CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces?
Post #1456858
Posted Monday, May 27, 2013 3:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Michael Meierruth (5/26/2013)
Where/what is this iFunction?
What's the difference between CreateCsv8K2DimArrayRandomAlphaNumeric and CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces?

Oooops.

I have re-posted the correct function in the post above. The difference is that the correct "...WithSpaces" version of the function adds random groups of 2 or more spaces into the text since replacing excess spaces is what we are trying to do. Sorry to have caused confusion.

And the iFunction is another "Modenism" to allow the use of certain operators that won't usually work inside functions. Brilliant idea and one I use so routinely I simply forgot about it!


CREATE VIEW [dbo].[iFunction] AS
/**********************************************************************************************************************
Purpose:
This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
a thing directly in the function. This view also solves the same problem for GETDATE().

Usage:
SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
SELECT MyDate FROM dbo.iFunction; --Returns a Date

Revision History:
Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code. No logic changes.
**********************************************************************************************************************/

SELECT MyNewID = NEWID(),
MyDate = GETDATE();

GO


Post #1457150
Posted Monday, May 27, 2013 3:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:33 AM
Points: 1,821, Visits: 2,173
Jeff Moden (5/25/2013)
Thak you very much for the feedback and for stopping by. I hope you're talking about the "Nested Replace" solution and not the one in the article. As happens many times, the discussion led to a solution better than the one in the article. The introduction in the article has a link to the "Nested Replace" soultion.


I was indeed talking about the nested replace solution. I marveled at the technique of the approach, the use two place pattern that reduces a problem of unknown size to a problem of a two char pattern repeated an unknown number of times. Once one is able to make that leap, the rest just snaps into place. Thanks Jeff.


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1457151
Posted Tuesday, May 28, 2013 3:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 537, Visits: 2,105
Steven Willis (5/26/2013)
I'm late to this party, but here's my entirely TSQL offering to remove duplicate spaces. Most of the code below has been not so shamelessly borrowed from Jeff Moden's work, especially the famous DelimitedSplit8K function which I have found to be one of the most useful tools in the toolbox.

I have no idea how this compares to the other solutions but I thought I'd just throw it into the mix. It's based mostly off of some other variations I've developed from the DelimitedSplit8K function and I think it takes a different approach to the problem. Basically, it splits the string and then puts it back together again. Whether that's better or not I'll let the scorekeeper determine.
 

OK. Yours is 6344ms. Mine is 2594ms.
Post #1457234
Posted Tuesday, May 28, 2013 8:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Michael Meierruth (5/28/2013)
Steven Willis (5/26/2013)
I'm late to this party, but here's my entirely TSQL offering to remove duplicate spaces. Most of the code below has been not so shamelessly borrowed from Jeff Moden's work, especially the famous DelimitedSplit8K function which I have found to be one of the most useful tools in the toolbox.

I have no idea how this compares to the other solutions but I thought I'd just throw it into the mix. It's based mostly off of some other variations I've developed from the DelimitedSplit8K function and I think it takes a different approach to the problem. Basically, it splits the string and then puts it back together again. Whether that's better or not I'll let the scorekeeper determine.
 

OK. Yours is 6344ms. Mine is 2594ms.

Thanks for taking the time to test this. My initial test using client statistics did show your method to be faster on my 1000 row test table. What testing method did you use? Do you think scalability becomes a factor for any of these methods where one might work better than another for a very large or very wide table? Not disputing the results--just wondering.

 
Post #1457363
« Prev Topic | Next Topic »

Add to briefcase «««3738394041

Permissions Expand / Collapse