UDF Works with literal value but with Dynamic Values

Question

Post reply

UDF Works with literal value but with Dynamic Values

YSLGuru

SSC-Insane

Points: 21661
More actions
August 17, 2011 at 2:17 pm

#146069

I know this shoudl work because I found it on a TSLQL Help site were several talked about it and I can get it tow ork when the UDF is passed a literal value but not if its passed a value in the querys results.
The UDF:
DROP FUNCTION dbo.udfMY_TRIM
GO
CREATE FUNCTION dbo.udfMY_TRIM(@MYSTR VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
SET @MYSTR = LTRIM(RTRIM(@MYSTR))
WHILE(CHARINDEX(SPACE(2), @MYSTR) <> 0)
SET @MYSTR = SUBSTRING(@MYSTR,1, CHARINDEX(SPACE(2), @MYSTR) ) + LTRIM(SUBSTRING(@MYSTR, CHARINDEX(SPACE(2), @MYSTR), 1000))
RETURN @MYSTR
END
If I use the UDF like this:
SELECT dbo.udfMY_TRIM(T.sCol1)
FROM TABLE T1
WHERE T1.ID = 1 /*limits result set to a single row*/
The value returned by the UDF is the exact same as whats passed to it. If I pass to the UDF the actual text contained in T1.sCol1 like below then the result is the same value with the excess spaces removed:
SELECT dbo.udfWAM_Trim('Here is some spaces - to test this with')
The above prodcues the result: Here is some spaces - to test this with
What am I doing wrong? What simple detail am I missing?
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

YSLGuru SSC-Insane Points: 21661 More actions · Answer 1

ISSUE RESOLVED:

SOLUTION: Never Take Vendors Word Again Regarding A Problem, always verify

Back story (for the curious): A third party vendor that provides services for our accounting database pointed out some problem data (data they generated thru their service) which had an excess of space characters, 32 to be exact. I took the vendors word and treated it like 32 spaces only to finally realize its not all spaces but mostly non-printables. When you copy and paste the fields contents from the grid results window (In SSMS Query tool) into the query window above the white spaces are treated as CHAR(32) but what’s actually in the VARCHAR field is not CHAR(32).

These are the time that try DB’s patients.

Kindest Regards,

Just say No to Facebook!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Glad you solved your issue but you could use a nested replace instead of a scalar function in this case to produce the same results.

declare @MyVal varchar(50) = 'Here is some spaces - to test this with'

SELECT replace(REPLACE(@MyVal, ' ', ' '), ' ', ' ')

No looping, no scalar function slowness.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 3

I absolutely agree with Sean. Please see the following article and pay particular attention to the "Prologue" at the beginning of the article...

http://www.sqlservercentral.com/articles/T-SQL/68378/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

Sean Lange (8/18/2011)
Glad you solved your issue but you could use a nested replace instead of a scalar function in this case to produce the same results.
declare @MyVal varchar(50) = 'Here is some spaces - to test this with'
SELECT replace(REPLACE(@MyVal, ' ', ' '), ' ', ' ')
No looping, no scalar function slowness.

Thanks for the recomendation Sean.

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 5

Jeff Moden (8/18/2011)
I absolutely agree with Sean. Please see the following article and pay particular attention to the "Prologue" at the beginning of the article...
http://www.sqlservercentral.com/articles/T-SQL/68378/

I swear I did not see that article when I initially searched on the SSC website on this multiple sapces issue.

Thanks Jeff.

Kindest Regards,

Just say No to Facebook!