March 18, 2002 at 1:39 pm
Hello everyone,
This is my first foray in creating a UDF. I was really looking forward to this feature. I've created what I thought was a simple function
/****** Object: User Defined Function cyborgmgr.cv2mx Script Date: 3/18/2002 11:26:57 AM ******/
CREATE FUNCTION cyborgmgr.cv2mx (@exp1 as varchar(255))
RETURNS varchar(255) AS
BEGIN
DECLARE @lgthSMALLINT
DECLARE @posTinyInt
Declare @loc TinyInt
--Declare @exp1 varchar(255)
set @pos = 0
set @loc= CHARINDEX(' ',@exp1,@pos)
While (@loc <> 0)
Begin
set @exp1 = stuff(@exp1,@loc+1,1, upper( substring(@exp1,@loc+1,1) ) )
set @pos = @loc + 1
set @loc= CHARINDEX(' ',@exp1,@pos)
end
return @exp1
END
====
To convert a string to mixed case.
When used in my select stmt. the run seems to go on forever. (I quit after 9 minutes).
The select is simpler:
Select cyborgmgr.cv2mix(Address_1) from tbl_current_employee.
Obviously, I've missed a critical bit of minutae here. Can anyone explain this to me or point me in the right direction?
December 8, 2002 at 8:39 pm
There are several scripts and articles dealing with T-SQL and strings. A couple of them address this. Look over there for more info.
December 9, 2002 at 4:51 am
If you can stay away from UDF... Build a SP instead. I have had some pretty bad experiences with UDF and you don't know what it is doing because they don't show up in de QUERY plan.
By deleting some UDF's and rewrite them into A stored procedure i was able to solve a lot op Performance problems.....
December 9, 2002 at 7:04 am
Try this instead.
One word or warning, TSQL is very slow for operations such as this, so should only be done on a small sets of data
running
select dbo.function(column) from mytable
will run a lot slower than
select column from mytable
BEWARE
DECLARE @lgth SMALLINT
DECLARE @pos TinyInt
Declare @loc TinyInt
if substring(@exp1,1,1) <> ' '
set @exp1 = stuff(@exp1,1,1, upper( substring(@exp1,1,1) ) )
set @pos = 0
set @loc= CHARINDEX(' ',@exp1,@pos)
While (@loc <> 0 AND @loc < LEN(@exp1))
Begin
set @exp1 = stuff(@exp1,@loc+1,1, upper( substring(@exp1,@loc + 1,1) ) )
set @pos = @loc + 1
set @loc= CHARINDEX(' ',@exp1,@pos)
select @loc, @pos, @exp1
end
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply