How to trim a column in user defined function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to trim a column in user defined function

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-06-07 : 15:05:21

I have the following function want to trim the TaskCode and TaskName.
Tried to use this: rtrim(isnull(tsk.[TaskName],''))

But i am getting an error message:

Can you please tell me how to use thr trim in userdefined functions:
**********************************************************************
---This function is used to get the Other Documents Records via CNID info to show under report
---Construction Contract Modification for MTA NY field name issue no.s
CREATE FUNCTION dbo.ccsMTACCMFunctionCost(@LinkModuleID integer)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + tsk.[TaskCode] + ' - ' + tsk.[TaskName] + ' - ' + CAST(cst.[Quantity] AS VARCHAR(12)) + ' - ' + cst.[MetricType] + ' - '
+ CAST(cst.[Quantity] AS VARCHAR(12)) + ' - ' +
CAST(cst.[UnitPrice] AS VARCHAR(12)) + ' - ' + CAST(cst.[Amount] AS VARCHAR(12)) + char(13)
from TAB_ccsNetCostDetails cst, TAB_ccsNetTasks tsk where ModuleRecordID = @LinkModuleID and EstimateType='final'

If @s IS NOT NULL AND @s <> ''
BEGIN
SELECT @s = left(@s, Datalength(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END
********************************************

Thank you very much for the info.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-07 : 15:31:09

The problem lies that your concatenated string @s has problems after CHAR(13).
The "FROM TAB_" and so on should also be part of the string.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-08 : 03:19:46

Cant you do the concatenation in front end application?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 03:56:45

[code]SELECT @s=@s + rtrim(tsk.[TaskCode]) + ' - ' + rtrim(tsk.[TaskName])[/code]
Do you need the record where TaskCode and TaskName is null ? If not, add to the where clause where TaskCode is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources