[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

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

0
irosa
Asked:
irosa
  • 3
  • 3
1 Solution
 
SharathData EngineerCommented:
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?
0
 
irosaAuthor Commented:
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

0
 
irosaAuthor Commented:
The variables are being passed in to the function in the query.

SELECT SubscriberID
      FROM Subscribe
      WHERE dbo.listfind(UserLists,'#Val(ecid)#',',') > 0
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
check this.
CREATE FUNCTION listFind(
list VARCHAR(8000), 
value VARCHAR(255),
delimiter VARCHAR(10))
 
RETURNS int
BEGIN 
 
SET @pos = 0;
 
WHILE (LENGTH(list) > 0)
DO
	SET @pos = @pos + 1;
	IF LOCATE(delimiter,list) > 0
		THEN
			SET @item = SUBSTRING(list,1,(LOCATE(delimiter, list)-1));
			SET list = SUBSTRING(list,(LOCATE(delimiter, list) + LENGTH(delimiter)),LENGTH(list));
 
			IF @item = value then 
			         RETURN @pos; end if;
	ELSE
        	SET @item = list;
	       SET list = null;
	        IF @item = value then
        		 return @pos; end if;
end if;
end while;
 
SET @pos = 0;
   RETURN @pos;
 
 end;

Open in new window

0
 
irosaAuthor Commented:
Thank you for the fast turnaround.  Nice work.
0
 
SharathData EngineerCommented:
glad to help you.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now