Link to home
Start Free TrialLog in
Avatar of irosa
irosa

asked on

Convert MS SQL UDF to MySQL

Can someone help convert this MS SQL UDF to a MySQL 5.0 UDF?
@delimiter VARCHAR(10))
 
RETURNS int
 
AS
BEGIN 
-- 2 declarations
-- @pos will be the location in the list
DECLARE @pos int
-- @item will contain items from the list
DECLARE @item VARCHAR(255)
-- set @pos to 0 by default
SELECT @pos = 0
 
-- Loop over the commadelimited list 
WHILE (DATALENGTH(@list) > 0)
BEGIN    
-- this is the current position of @item in the list
SELECT @pos = @pos + 1
-- IF the list length is greater than 1
IF CHARINDEX(@delimiter,@list) > 0
BEGIN
    -- item contains the value to compare
SELECT @item =
SUBSTRING(@list,1,(CHARINDEX(@delimiter, @list)-1))
    -- remove that particular item from the list
SELECT @list =
SUBSTRING(@list,(CHARINDEX(@delimiter, @list) + DATALENGTH(@delimiter)),DATALENGTH(@list))
      
       -- if the 2 items are the same return the position
IF @item = @value 
         RETURN @pos
END
 
ELSE
   -- there is only 1 item in the list (or perhaps a zero length string)
   -- so we just compare them directly
BEGIN
        SELECT @item = @list
       SELECT @list = null
        IF @item = @value
         return @pos
END
 
END
-- if we get this far the item is not in the list
SELECT @pos = 0
   RETURN @pos
END

Open in new window

Avatar of Sharath S
Sharath S
Flag of United States of America image

Your MS SQL UDF itself has some errors. Please rectify those first.
1) CREATE FUNCTION fn_FunctionName is missing.
2)  Variables @list , @delimiter and @value are not declared. Are you passing these variables to the function or declaring them inside the function definition?
Avatar of irosa
irosa

ASKER

The first 3 lines of the function were not copied.  I have corrected it and the complete function is now attached.
CREATE FUNCTION dbo.listFind(
@list VARCHAR(8000), 
@value VARCHAR(255),
@delimiter VARCHAR(10))
 
RETURNS int
 
AS
BEGIN 
-- 2 declarations
-- @pos will be the location in the list
DECLARE @pos int
-- @item will contain items from the list
DECLARE @item VARCHAR(255)
-- set @pos to 0 by default
SELECT @pos = 0
 
-- Loop over the commadelimited list 
WHILE (DATALENGTH(@list) > 0)
BEGIN    
-- this is the current position of @item in the list
SELECT @pos = @pos + 1
-- IF the list length is greater than 1
IF CHARINDEX(@delimiter,@list) > 0
BEGIN
    -- item contains the value to compare
SELECT @item =
SUBSTRING(@list,1,(CHARINDEX(@delimiter, @list)-1))
    -- remove that particular item from the list
SELECT @list =
SUBSTRING(@list,(CHARINDEX(@delimiter, @list) + DATALENGTH(@delimiter)),DATALENGTH(@list))
      
       -- if the 2 items are the same return the position
IF @item = @value 
         RETURN @pos
END
 
ELSE
   -- there is only 1 item in the list (or perhaps a zero length string)
   -- so we just compare them directly
BEGIN
        SELECT @item = @list
       SELECT @list = null
        IF @item = @value
         return @pos
END
 
END
-- if we get this far the item is not in the list
SELECT @pos = 0
   RETURN @pos
END

Open in new window

Avatar of irosa

ASKER

The variables are being passed in to the function in the query.

SELECT SubscriberID
      FROM Subscribe
      WHERE dbo.listfind(UserLists,'#Val(ecid)#',',') > 0
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of irosa

ASKER

Thank you for the fast turnaround.  Nice work.
glad to help you.