'SQL Query Analyzer' and INSTR

[David Klemitz]

Hi folks,

I'm kind of new to this so might be missing something obvious. I
loaded the script that makes up the 'instr' function (from SQL Server
Worldwide Users group) that allows me to search for the last occurence
of a substring in a character string.

I loaded the script using 'SQL Query Analyzer' and it appeared to
accept the code. When I tried to call the function using the following
I get ''INSTR' is not a recognized function name'.

update tbl_songs
set OldName=substring(SongPath, INSTR(SongPath, '\', -1),
datalength(SongPath) - 1)

I have seen other posts that recommend the use of 'patindex' and
'charindex' instead- however I am looking for the *last* occurence of
'\' not the first. Does the instr script need to be registered somehow
within SQL Server 2000 ?!?

any help appreciated

regards

David

[David Portas]

User-defined functions have to be qualified with the owner name. For
example:

dbo.Instr(SongPath, '\', -1)

UDFs normally perform a lot slower than using the equivalent built-in
functions. You may be better off with something like this:

UPDATE tbl_songs
SET oldname =
RIGHT(songpath,CHARINDEX('\',REVERSE('\'+songpath))-1)

Which sets Oldname equal to the substring following the last '\' in Songpath

[David Klemitz]

Post by David Portas
User-defined functions have to be qualified with the owner name. For
dbo.Instr(SongPath, '\', -1)
UDFs normally perform a lot slower than using the equivalent built-in
UPDATE tbl_songs
SET oldname =
RIGHT(songpath,CHARINDEX('\',REVERSE('\'+songpath))-1)
Which sets Oldname equal to the substring following the last '\' in Songpath

thanks, that did the trick. Re UDF's is there a reference somewhere as
to how to implement these in SQL Server ?

thanks again guys

regards

David

[David Portas]

Have you looked up CREATE FUNCTION in Books Online?

Also:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_08_460j.asp

[Dejan Sarka]

Check whether the UDF is created in the db you are using and use two-part
name to cll it (if dbo is the owner, use dbo.Instr(), for example).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

Post by David Klemitz
Hi folks,
I'm kind of new to this so might be missing something obvious. I
loaded the script that makes up the 'instr' function (from SQL Server
Worldwide Users group) that allows me to search for the last occurence
of a substring in a character string.
I loaded the script using 'SQL Query Analyzer' and it appeared to
accept the code. When I tried to call the function using the following
I get ''INSTR' is not a recognized function name'.
update tbl_songs
set OldName=substring(SongPath, INSTR(SongPath, '\', -1),
datalength(SongPath) - 1)
I have seen other posts that recommend the use of 'patindex' and
'charindex' instead- however I am looking for the *last* occurence of
'\' not the first. Does the instr script need to be registered somehow
within SQL Server 2000 ?!?
any help appreciated
regards
David