September 16, 2012 at 8:31 am
I 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.ROOMKEY
FROM HOST0140
INNER JOIN HOST0120
ON HOST0120.OWNERKEY=HOST0140.PERSONKEY
INNER JOIN HOST9006
ON HOST9006.KEYVALUE=HOST0120.MTGSTATE
INNER JOIN HOST0110
ON HOST0110.ROOMKEY=HOST0120.ROOMKEY
WHERE 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 START
It returns everything I want it to. The issue is that the HOST0140.NAME returns the value as:
Lastname, Firstname
I desperatly need the code above to be adapted to reverse this and remove the comma. So the NAME column shows:
Firstname Lastname
I am tearing my haid out at the moment lol.
Many Thanks
September 16, 2012 at 9:30 am
Try this. If it works fine, make it a UDF and call it passing in your name field.
declare
@Input varchar(200)
,@Delimiter varchar(5)
,@Output varchar(150)
set @Input = 'Doe, John'
set @Delimiter = ','
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
print SUBSTRING(@Output,0,LEN(@Output))
Mark
September 16, 2012 at 10:07 am
Hi,
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.
THanks
September 16, 2012 at 10:40 am
Sorry about that.
UDF = User Defined Function
Execute this in your database:
create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))
returns varchar(100)
as
begin
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
This will create the UDF for you.
And then change this part of your select query:
HOST0140.NAME
To this:
dbo.udf_ReverseNames(HOST0140.NAME, ',') as Name
That should return the name in reverse and strip off the comma.
Mark
September 16, 2012 at 6:15 pm
Mark Eckeard (9/16/2012)
Sorry about that.UDF = User Defined Function
Execute this in your database:
create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))
returns varchar(100)
as
begin
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
This will create the UDF for you.
And then change this part of your select query:
HOST0140.NAME
To this:
dbo.udf_ReverseNames(HOST0140.NAME, ',') as Name
That should return the name in reverse and strip off the comma.
Mark
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.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2012 at 7:41 pm
Here'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...
--===== 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
;
WITH
cteTally 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
;
Here's an iTVF being used as an iSF (see http://www.sqlservercentral.com/articles/T-SQL/91724/
for more on those)...
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
;
Now, a test to compare the two. The @Bitbucket variable takes the display time out of the picture.
--===== 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));
Here are the results from that test. "ReverseName" is new function with no While Loop.
========== ReverseName ========================================
Duration (ms): 450
========== udf_ReverseNames ===================================
Duration (ms): 2123
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2012 at 3:15 am
Forgive my innocent question here but why a FUNCTION at all?
Just replace:
HOST0140.NAME
with:
NAME=RIGHT(HOST0140.NAME, LEN(HOST0140.NAME)-(1+CHARINDEX(', ', HOST0140.NAME))) + ' ' +
LEFT(HOST0140.NAME, CHARINDEX(', ', HOST0140.NAME) - 1)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 17, 2012 at 7:22 am
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
September 17, 2012 at 1:29 pm
Mark Eckeard (9/17/2012)
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
Thanks for the feedback, Mark. I absolutely 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. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply