UDF does not work in SELECT statement
-
Here's the question in a nutshell:
I have a User Defined Function, that takes a TEXT data type and parses through it to find a certain string and returns a VARCHAR(12) portion of the string. It works fine when I call it and explicitly pass it the text string (from Query Analyzer), but when I use it in a select statement it does not work.
It contains a WHILE loop, so I'm thinking that might be the problem. I have debugged this thing every which way I can think of and can't get it.
Example:
1. This works: SELECT JobNumber, MFJBS.dbo.GetQuoteNumberFromUserText('\par C06D3102-06D3102 \par QUOTATION NO.: 53040-1Q-4 \par REF: 880780 \par PAINT') FROM JobMaster WHERE JobNumber = '06D3104'
It returns: 06D3104 | 53040-1Q-4
2. This doesn't work: SELECT JobNumber, MFJBS.dbo.GetQuoteNumberFromUserText(UserText) FROM JobMaster WHERE JobNumber = '06D3104'
It returns: 06D3104 | 53040-1Q-4 \
It doesn't strip off the last two characters ' \'.
I can post the SQL for the UDF if you need me to. Just wanted to make sure there wasn't anything glaringly wrong with the way I'm calling it. I have full permissions and am using SQL Server 2000.
Thanks,
Jim
PS. I have even tried changing the input var type to varchar(255) but the same results.
-
If the udf returns rows, then you cannot use it like this in 2000. In 2005 you could use the cross apply operator but there's no workaround in 2000 other than rethinking the process. And I can't do that for you unless I have more info on what you are trying to do!
-
It's a scalar function. Here is the sql:
CREATE FUNCTION GetQuoteNumberFromUserText
(@UserText TEXT)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @TempUserText VARCHAR(255)
DECLARE @Result VARCHAR(12)
DECLARE @Var CHAR(1)
DECLARE @Counter TINYINT
IF @UserText IS NULL
SET @Result = ''
ELSE
BEGIN
SET @Var = ''
--Check to see if the word QUOTATION is found:
SET @Counter = CHARINDEX('QUOTATION', @UserText) + 16
IF @Counter > 0
--If it is found, then start processing:
BEGIN
--Note that the longest string we expect is 12 chars (this could be changed):
SET @Result = SUBSTRING(@UserText, @Counter, 12)
--If the first 5 chars are not numeric, return an empty string:
IF ISNUMERIC(SUBSTRING(@Result, 1, 5)) = 0
SET @Result = ''
ELSE
BEGIN
--Set the @Counter variable:
SET @Counter = 6
--Get the 6th character:
SET @Var = SUBSTRING(@Result, @Counter, 1)
--Continue adding characters until you hit a space:
WHILE @Var <> ' '
BEGIN
SET @Counter = @Counter + 1
SET @Var = SUBSTRING(@Result, @Counter, 1)
END
SET @Result = SUBSTRING(@Result, 1, @Counter - 1)
END
END
ELSE
SET @Result = ''
END
RETURN @Result
END
-Jim
-
What error are you getting?
-
I'm not getting an error, just not the expected results. When I run the function sending it a text string it works. When I run it and pass it the column value (equivalent to the text string I just used) it doesn't work. See the above examples in the previous post.
Thanks-
-
Run this:
SELECT JobNumber, UserText, MFJBS.dbo.GetQuoteNumberFromUserText(UserText) FROM JobMaster WHERE JobNumber = '06D3104'
and see what exactly you function does not handle.
Your UserText must not be the same as the test string you paste in QA.
_____________
Code for TallyGenerator -
That's what I'm thinking. When I copy and paste it, it must change a CR or LF to a space.
Jim
-
Set in QA "Results to Text" and copy-paste from there.
_____________
Code for TallyGenerator -
Sure enough - they show up as new lines!
Thanks.
-
And your function is quite bad.
I definitely would not use it.
For example:
SET @Counter = CHARINDEX('QUOTATION', @UserText) + 16
IF @Counter > 0
...
Can you provide an example when @Counter gonna be <= 0?
_____________
Code for TallyGenerator -
Yes, if the word 'QUOTATION' is not found, this is common.
-
And?
If the word 'QUOTATION' is not found, then @Counter = ?
Check your calculations.
Another issue - CHARINDEX does not work with TEXT. It implicitly converts it into VARCHAR(8000).
Continue?
_____________
Code for TallyGenerator -
I don't understand your response. If the word 'QUOTATION' is not found then @Counter = 0.
The answer to my original problem, as it turns out, was because I was not testing for the right characters. I was testing for a space, but the string contained Carriage Returns. This is what caused the discrepancy when executing the function with a text string I pasted in and the actual column data. When I pasted the column data in, it converted the CR's to spaces. Anyways, here is the new code, which works.
Thanks for your help! Jim
ALTER FUNCTION GetQuoteNumberFromUserText
(@UserText TEXT)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @Result VARCHAR(12)
DECLARE @Counter SMALLINT
DECLARE @TestChar CHAR(1)
DECLARE @Found BIT
SET @Result = ''
SET @Found = 0
IF @UserText IS NOT NULL
BEGIN
--Check to see if the word QUOTATION is found:
SET @Counter = CHARINDEX('QUOTATION', @UserText) + 16
IF @Counter > 0
--If it is found, then start processing:
BEGIN
--Note that the longest string we are getting is 12 chars (this could be changed):
SET @Result = SUBSTRING(@UserText, @Counter, 12)
--If the first 5 chars are numeric, then it is a valid quote number
--and we can get the whole number:
IF ISNUMERIC(LEFT(@Result, 5)) = 1
BEGIN
SET @Counter = 6
SET @TestChar = SUBSTRING(@Result, @Counter, 1)
WHILE @Found = 0
BEGIN
--If the character is a tab, CR, LF, or space, then we have gotten
--to the end of the quote number and can break out of the loop
IF ASCII(@TestChar) IN (10, 13, 9, 32)
SET @Found = 1
SET @Counter = @Counter + 1
SET @TestChar = SUBSTRING(@Result, @Counter, 1)
END
SET @Result = LEFT(@Result, @Counter - 1)
END
ELSE
SET @Result = ''
END
END
RETURN @Result
END
-
Have you looked at your code???
SET @Counter = CHARINDEX('QUOTATION', @UserText) + 16
If there is no word 'QUOTATION' then CHARINDEX = 0, @Counter = 0 + 16 = 16
What dom't you understand here?
Another issue into your collection:
ISNUMERIC('123D4') = 1, ISNUMERIC('12E34') = 1
Is it what are looking for?
_____________
Code for TallyGenerator -
To reply to the isnumeric problem :
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE XType = 'FN' AND Name = 'fnIsAllNumbers')
DROP FUNCTION dbo.fnIsAllNumbers
GO
CREATE FUNCTION dbo.fnIsAllNumbers (@Str AS varchar(8000))
RETURNS INT
AS
BEGIN
RETURN (SELECT CASE WHEN @STR NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END)
END
GO
SELECT dbo.fnIsAllNumbers('12.94')
--0
SELECT dbo.fnIsAllNumbers('12E94')
--0
SELECT dbo.fnIsAllNumbers('0x234')
--0
SELECT dbo.fnIsAllNumbers('0123456789')
--1
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply