InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 09:29:02 GMT20http://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspx[quote][b]Mark Eckeard (9/17/2012)[/b][hr]Jeff - thanks, I'll keep this in mind in case I have to do something similar in the future.The advantage to making it a UDF is for code re-use. I was thinking he may need to this in other places and it would make it easier although he could certainly use reverse the words directly in his query if necessary.Mark[/quote]Thanks for the feedback, Mark. I [i]absolutely [/i]agree that it should be a UDF for the very reasons you stated. That's why I included my code as a UDF... just not a scalar one. :-)Mon, 17 Sep 2012 13:29:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxJeff - thanks, I'll keep this in mind in case I have to do something similar in the future.The advantage to making it a UDF is for code re-use. I was thinking he may need to this in other places and it would make it easier although he could certainly use reverse the words directly in his query if necessary.MarkMon, 17 Sep 2012 07:22:35 GMTMark Eckeardhttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxForgive my innocent question here but why a FUNCTION at all?Just replace:[code="sql"]HOST0140.NAME[/code]with:[code="sql"]NAME=RIGHT(HOST0140.NAME, LEN(HOST0140.NAME)-(1+CHARINDEX(', ', HOST0140.NAME))) + ' ' + LEFT(HOST0140.NAME, CHARINDEX(', ', HOST0140.NAME) - 1)[/code]Mon, 17 Sep 2012 03:15:40 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxHere's a different method that's faster. Of course, I can't claim performance improvements unless I can prove it. To do that, we'll setup a 100,000 row test table, like this...[code="sql"]--===== Conditionally drop and repopulate the test table -- to make reruns in SSMS easier. We're just building -- test data here. THIS IS NOT A PART OF THE SOLUTION. IF OBJECT_ID('tempdb..#HOST0140','U') IS NOT NULL DROP TABLE #HOST0140;WITHcteTally AS( SELECT TOP 100000 N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2) SELECT NAME = 'LastName'+CAST(N AS VARCHAR(10)) + ', ' + 'FirstName'+CAST(N AS VARCHAR(10)) INTO #HOST0140 FROM cteTally;[/code]Here's an iTVF being used as an iSF (see [url]http://www.sqlservercentral.com/articles/T-SQL/91724/ [/url] for more on those)...[code="sql"] CREATE FUNCTION dbo.ReverseName (@pString VARCHAR(8000),@pDelimiter VARCHAR(5))RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH cteFindDelimiter AS( SELECT DelimiterPosition = CHARINDEX(@pDelimiter,@pString)) SELECT ReversedName = CASE WHEN DelimiterPosition > 0 THEN LTRIM(SUBSTRING(@pString,DelimiterPosition+LEN(@pDelimiter),8000)) + ' ' + SUBSTRING(@pString,1,DelimiterPosition-1) ELSE @pString END FROM cteFindDelimiter;[/code]Now, a test to compare the two. The @BitBucket variable takes the display time out of the picture.[code="sql"]--===== Declare a timer variable.DECLARE @StartTime DATETIME;--===== Create variable to take display times out of the picture.DECLARE @BitBucket VARCHAR(8000);RAISERROR('========== ReverseName ========================================',0,1) SELECT @StartTime = GETDATE(); SELECT @BitBucket = r.ReversedName FROM #HOST0140 h CROSS APPLY dbo.ReverseName(h.Name,',') r; PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));RAISERROR('========== udf_ReverseNames ===================================',0,1) SELECT @StartTime = GETDATE(); SELECT @BitBucket = dbo.udf_ReverseNames(h.Name,',') FROM #HOST0140 h; PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));[/code]Here are the results from that test. "ReverseName" is new function with no While Loop.[code="plain"]========== ReverseName ========================================Duration (ms): 450========== udf_ReverseNames ===================================Duration (ms): 2123[/code]Sun, 16 Sep 2012 19:41:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspx[quote][b]Mark Eckeard (9/16/2012)[/b][hr]Sorry about that.UDF = User Defined FunctionExecute this in your database:[code="other"]create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))returns varchar(100)asbegin declare @Output varchar(150) WHILE LEN(@Input) > 0 BEGIN IF CHARINDEX(@Delimiter, @Input) > 0 BEGIN SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'') SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input)) END ELSE BEGIN SET @Output = @Input + ' ' + ISNULL(@Output,'') SET @Input = '' END END return SUBSTRING(@Output,0,LEN(@Output))end[/code]This will create the UDF for you.And then change this part of your select query:HOST0140.NAMETo this:dbo.udf_ReverseNames(HOST0140.NAME, ',') as NameThat should return the name in reverse and strip off the comma.Mark[/quote]Oh, be careful now, Mark. The use of While loops here does two bad things...1. They themselves are slow.2. To use them in a UDF requires that the UDF be slower because it will be either a Scalar UDF or a Multi-Line Table Value Function. It really needs to be written as an "iSF".Please see the following article for more on all of that.[url]http://www.sqlservercentral.com/articles/T-SQL/91724/[/url]Sun, 16 Sep 2012 18:15:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxSorry about that.UDF = User Defined FunctionExecute this in your database:[code="other"]create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))returns varchar(100)asbegin declare @Output varchar(150) WHILE LEN(@Input) > 0 BEGIN IF CHARINDEX(@Delimiter, @Input) > 0 BEGIN SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'') SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input)) END ELSE BEGIN SET @Output = @Input + ' ' + ISNULL(@Output,'') SET @Input = '' END END return SUBSTRING(@Output,0,LEN(@Output))end[/code]This will create the UDF for you.And then change this part of your select query:HOST0140.NAMETo this:dbo.udf_ReverseNames(HOST0140.NAME, ',') as NameThat should return the name in reverse and strip off the comma.MarkSun, 16 Sep 2012 10:40:52 GMTMark Eckeardhttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxHi,Thanks for your reply. Unfortunatlly i dont even know what a UDF is...I am extreamlly new to SQL - I am doing this as a one off and need to get it working in the next two hours. I may well loose my mind soon lol.THanksSun, 16 Sep 2012 10:07:18 GMTjames.martin 82196http://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxTry this. If it works fine, make it a UDF and call it passing in your name field.[code="other"]declare @Input varchar(200) ,@Delimiter varchar(5) ,@Output varchar(150)set @Input = 'Doe, John'set @Delimiter = ','WHILE LEN(@Input) > 0BEGINIF CHARINDEX(@Delimiter, @Input) > 0BEGINSET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))ENDELSEBEGINSET @Output = @Input + ' ' + ISNULL(@Output,'')SET @Input = ''ENDENDprint SUBSTRING(@Output,0,LEN(@Output))[/code]MarkSun, 16 Sep 2012 09:30:01 GMTMark Eckeardhttp://www.sqlservercentral.com/Forums/Topic1359885-1292-1.aspxI dont know if this is any help but this is the code I started with:SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, HOST0140.NAME, dateadd(mi, HOST0120.STARTMINS, HOST0120.MTGDATE) AS ASTART, dateadd(mi, HOST0120.ENDMINS, HOST0120.MTGDATE) AS AFINISH, CONVERT (VARCHAR(5), dateadd(mi, HOST0120.STARTMINS, HOST0120.MTGDATE) ,108) AS START, CONVERT (VARCHAR(5), dateadd(mi, HOST0120.ENDMINS, HOST0120.MTGDATE) ,108) AS FINISH, HOST0120.MTGKEY, HOST0120.HIPTYPE, HOST0120.ROOMKEYFROM HOST0140INNER JOIN HOST0120ON HOST0120.OWNERKEY=HOST0140.PERSONKEYINNER JOIN HOST9006ON HOST9006.KEYVALUE=HOST0120.MTGSTATEINNER JOIN HOST0110ON HOST0110.ROOMKEY=HOST0120.ROOMKEYWHERE CANCELSTATE='0'AND MTGDATE >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)AND MTGDATE < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)AND MTGSTATE <> '11'ORDER BY STARTIt returns everything I want it to. The issue is that the HOST0140.NAME returns the value as:Lastname, FirstnameI desperatly need the code above to be adapted to reverse this and remove the comma. So the NAME column shows:Firstname LastnameI am tearing my haid out at the moment lol.Many ThanksSun, 16 Sep 2012 08:31:25 GMTjames.martin 82196