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
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
ASKER
The variables are being passed in to the function in the query.
SELECT SubscriberID
FROM Subscribe
WHERE dbo.listfind(UserLists,'#V al(ecid)#' ,',') > 0
SELECT SubscriberID
FROM Subscribe
WHERE dbo.listfind(UserLists,'#V
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the fast turnaround. Nice work.
glad to help you.
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?