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.