Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lastname, Firstname switch


Lastname, Firstname switch

Author
Message
james.martin 82196
james.martin 82196
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Mark Eckeard
Mark Eckeard
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 505
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



james.martin 82196
james.martin 82196
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Mark Eckeard
Mark Eckeard
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 505
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71437 Visits: 40744
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71437 Visits: 40744
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6335 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Mark Eckeard
Mark Eckeard
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 505
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71437 Visits: 40744
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search