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

Lastname, Firstname switch Expand / Collapse
Author
Message
Posted Sunday, September 16, 2012 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 10:05 AM
Points: 2, Visits: 1
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
Post #1359885
Posted Sunday, September 16, 2012 9:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
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



Post #1359892
Posted Sunday, September 16, 2012 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 10:05 AM
Points: 2, Visits: 1
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

Post #1359897
Posted Sunday, September 16, 2012 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
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




Post #1359901
Posted Sunday, September 16, 2012 6:15 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1359935
Posted Sunday, September 16, 2012 7:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1359943
Posted Monday, September 17, 2012 3:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1360041
Posted Monday, September 17, 2012 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
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



Post #1360190
Posted Monday, September 17, 2012 1:29 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1360429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse