Easy Question (I Think)!

[GDX]

I am trying to create a function in SQL that will return the position number of a pattern in a string but im not too good with SQL can someone help me? I called it Instr because i have a client that requires me to use this function...

CREATE FUNCTION INstr (@srch varchar,@data varchar)
RETURNS varchar
AS
BEGIN
return (PATINDEX ('%@srch%',@data))
GO

Note: @srch is the pattern its searching for and @data is the whole string, Thank you! Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[balves]

Well, a couple of things show right off the bat:

- PatIndex returns an Int but you're returing a Varchar.

- PatIndex('%@srch%', ...) is going to literally look for "@srch" in what @data evaluates to.

 

[balves]

Something like this should work:

CREATE FUNCTION INstr (@srch varchar, @data varchar)
RETURNS int
DECLARE @srchme varchar(30)
DECLARE @retcode int
AS
BEGIN
SET @srchme = '%' + @srch + '%'
set @retcode = patindex(@srchme, @data)
return @retcode
END
GO

 

[GDX]

Hi guys the function works now thanks a lot! but now its only returning exact matches not like matches even though i added the %%

ALTER FUNCTION INstr (@srch varchar, @data varchar)
RETURNS int
AS
BEGIN
DECLARE @srchme varchar(30);
DECLARE @retcode int;
SET @srchme = '%' + @srch + '%'
set @retcode = patindex(@srchme, @data)
return @retcode
END
GO

Any Ideas?
Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[balves]

I'm not sure what you mean. If I put this code in SQL Query Analyzer, I get the right result:

DECLARE @srch varchar(30)
DECLARE @data varchar(30)
DECLARE @srchme varchar(30)
DECLARE @retcode int

SET @srch = 'ABC'
SET @data = 'DEFDJABCDJDJ'
SET @srchme = '%' + @srch + '%'
set @retcode = patindex(@srchme, @data)
print @retcode

6

 

[SQLBill]

Gordon,

Are you using MS SQL or Oracle SQL????? I saw on one of your other posts that you said it was Oracle. If you are using Oracle, it's possible some of the commands, etc posted in this forum won't work for you. If it is Oracle, try the Oracle forums. If you are using the Microsoft SQL then just ignore my comments.

-Bill

 

[GDX]

Hi SQLBill,

Yea I know sorry about posting it in the wrong forum by the way, but this issue deals with MSSQL2K. Thanks Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371