August 22, 2012 at 11:35 am
You are welcome Miles,
I honestly only came across this a few months ago myself. I try to take portions of my code and revamp them based on new available functionality or methods I had missed to improve performance and size. I too would do loops myself until I came across this and laughed at myself when I discovered it had been there since Framework 2.0.
August 22, 2012 at 6:52 pm
Antares686 (8/22/2012)
nguyenthanh5 (8/20/2012)
Hi Jeff,Sorry I missed the point.
I have created a function in VB.NET to do just that. I tested and it's fast. Could you care to test it? I do not know if it is worth to make it CRL function. I do not know how to make CRL function in SQL yet. Below is my function:
Public Function ReplaceSpacesWithOne(ByVal dataIn As String) As String
' Imports system.text to use StringBuilder '( this line before your Class or Module)
' use space(1) to avoid typo, make sure one one space
Dim data() As String = dataIn.Split(Space(1))
Dim Result As New StringBuilder
data = dataIn.Split(Space(1))
For Each Str As String In data
If Not String.IsNullOrEmpty(Str) Then
Result.Append(Str + Space(1))
End If
Next
Return Result.ToString
End Function
I use tab to space out to make it easy to read, but after post the tab and space lost. Hope it's not hard to read.
Best regards,
Tom N
The key difference is the Replace method is in native T-SQL which can be done in any version of SQL Server. As for the above I would personally simplify to the below (C# example).
public string ReplaceSpacesWithOne(string strIn)
{
return string.Join(" ", strIn.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
}
Thanking you.
Use String.join together with Split make the code to online! CLR can be used on SQL Server 2005 or later.
VB.Net is verbal compares to C#. You inspire me to start using C#. (the trouble is my team mates use VB.net).
VB.Net:
Public Shared Function ReplaceSpacesBy1(ByVal dataIn As String) As String
ReplaceSpacesBy1 = String.Join(Space(1), dataIn.Split(New Char() {Space(1)}, StringSplitOptions.RemoveEmptyEntries))
End Function
Jeff,
I have dll with two functions - one with a loop and one without loop (Antares686's idea) and the class file but do not know how to attach to the post!
Best Regards,
Tom N
August 22, 2012 at 7:41 pm
nguyenthanh5 (8/22/2012)
Jeff,I have dll with two functions - one with a loop and one without loop (Antares686's idea) and the class file but do not know how to attach to the post!
Best Regards,
Tom N
I don't actually want the DLL's, Tom. Build the CLR functions on SQL Server and then script them out. You can either post them using the [ code = "sql" ] IFCode shortcut to the left of a new message window or you can just paste them as plain text.
Be sure to include the source code so someone else can have a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2012 at 11:31 pm
Jeff Moden (8/22/2012)
nguyenthanh5 (8/22/2012)
Jeff,I have dll with two functions - one with a loop and one without loop (Antares686's idea) and the class file but do not know how to attach to the post!
Best Regards,
Tom N
I don't actually want the DLL's, Tom. Build the CLR functions on SQL Server and then script them out. You can either post them using the [ code = "sql" ] IFCode shortcut to the left of a new message window or you can just paste them as plain text.
Be sure to include the source code so someone else can have a look.
Here is the script and code.
USE [databaseName]
GO
/****** Object: UserDefinedFunction [dbo].[ReplaceSpacesBy1SpaceS] Script Date: 08/23/2012 15:07:41 ******/
CREATE FUNCTION [dbo].[ReplaceSpacesBy1SpaceS](@dataIn [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRfunctions].[CLRfunctions.UserDefinedFunctions].[ReplaceSpacesBy1SpaceS]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'ReplaceSpacesBy1space.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'
source codes from VS 2008
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function ReplaceSpacesBy1SpaceS(ByVal dataIn As String) As SqlString
' short
Return New SqlString(String.Join(Space(1), dataIn.Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)))
End Function
Public Shared Function ReplaceSpacesBySpaceL(ByVal dataIn As String) As SqlString
' use imports system.text
' easy read and understand but long
Dim data() As String = dataIn.Split(" "c)
Dim Result As New StringBuilder
For Each Str As String In data
If Not String.IsNullOrEmpty(Str) Then
Result.Append(Str + Space(1))
End If
Next
Return New SqlString(Result.ToString.Trim())
End Function
End Class
regards
Tom N
August 23, 2012 at 7:13 pm
My apologies for not being clear on a couple of things...
First, I left spaces in the IFCodes I posted so they could be seen instead of working and I forgot to tell you that. Take the spaces out and magic will happen.
Second, there's a way to generate the script for a CLR that will contain the compiled binary and that's what I was looking for. I just can't remember off the top of my head how to do it. I believe you have to gen the assembly itself?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2012 at 10:52 pm
Jeff Moden (8/23/2012)
My apologies for not being clear on a couple of things...First, I left spaces in the IFCodes I posted so they could be seen instead of working and I forgot to tell you that. Take the spaces out and magic will happen.
Second, there's a way to generate the script for a CLR that will contain the compiled binary and that's what I was looking for. I just can't remember off the top of my head how to do it. I believe you have to gen the assembly itself?
Thanking you Jeff,
I'm a dud, take me long time to learn. I now can see IFCode shortcuts.
I got VS2008 made the assembly and deployed to SQL Server for me (my 1st time!). The other way is to compile source code and the result is dll. Then we can create assembly from the dll like :
CREATE ASSEMBLY CLRFunctions
FROM 'c:\myTestStuff \mySpaces.dll'
WITH PERMISSION_SET = SAFE; -- ref Create Assembly book online
I do know how to generate script CLR function including assembly yet. I will post such script if I can make it.
Thanks,
Tom N
January 23, 2013 at 7:50 pm
how about this...?
base on the fact that we have only odd and even number...
select '['+ rtrim(ltrim(replace(replace(replace(replace(' Homer Simpson is kewl ',' ','_'),'__',''),'_ ',' '),'_',' '))) +']'
January 23, 2013 at 8:01 pm
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)
January 28, 2013 at 1:37 am
This approach was published in a solution quite early in this discussion.
May 22, 2013 at 1:59 pm
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. 😉
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 25, 2013 at 1:40 pm
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
Change is inevitable... Change for the better is not.
May 26, 2013 at 1:42 am
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
May 26, 2013 at 5:59 am
Where/what is this iFunction?
What's the difference between CreateCsv8K2DimArrayRandomAlphaNumeric and CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces?
May 27, 2013 at 3:34 pm
Michael Meierruth (5/26/2013)
Where/what is this iFunction?What's the difference between CreateCsv8K2DimArrayRandomAlphaNumeric and CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces?
Oooops. :blush:
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
May 27, 2013 at 3:37 pm
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.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 391 through 405 (of 425 total)
You must be logged in to reply to this topic. Login to reply