First-timer: Removing last comma from a string
-
This is my first time posting here, and I was wondering if someone could help me. I am working with an alter-function that produces a list of names such:
lisa perkins, james bell, samual kirkwood,
I was wondering how to trim the last comma off. The routine is as follows:
ALTER
FUNCTION [capsf].[GetAttorneys]
(
@NewCaseId varchar
(25)
)
RETURNS VARCHAR
(2000)
AS
BEGIN
DECLARE
@itemList VARCHAR(8000)
SET
@itemList = ''
SELECT
@itemList = @itemList + DefenseAttyDesc.FName + ' ' + DefenseAttyDesc.LName + ', ' + char(13)
from
CaseToDefenseAttorneyMapping,DefenseAttyDesc
where
CaseToDefenseAttorneyMapping.DefAttyId = DefenseAttyDesc.DefAttyId
and
CaseToDefenseAttorneyMapping.NewCaseId = @NewCaseId
and
CaseToDefenseAttorneyMapping.TypeOfCourt = 'T'
RETURN
@itemList
-
DECLARE @itemList VARCHAR(8000)
--SET@itemList = ''SELECT
@itemList = ISNULL(@itemList+ ', ' + char(13), space(0)) + DefenseAttyDesc.FName + ' ' + DefenseAttyDesc.LName
from ...
_____________
Code for TallyGenerator -
Here you go:
ALTER FUNCTION [capsf].[GetAttorneys]
(
@NewCaseId varchar(25)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @itemList VARCHAR(8000)
SET @itemList = ''
SELECT @itemList = @itemList + DefenseAttyDesc.FName + ' ' + DefenseAttyDesc.LName + ', ' + char(13)
from CaseToDefenseAttorneyMapping,DefenseAttyDesc
where CaseToDefenseAttorneyMapping.DefAttyId = DefenseAttyDesc.DefAttyId
and CaseToDefenseAttorneyMapping.NewCaseId = @NewCaseId
and CaseToDefenseAttorneyMapping.TypeOfCourt = 'T'
If Right(ltrim(rtrim(@itemList)),1) =','
Begin
select @itemList = left(ltrim(rtrim(@itemList)),len(ltrim(rtrim(@itemList)))-1)
End
RETURN @itemList
-
I found ten centuries' solution to work very well, but I had a problem with veteran's. Thank you both very much.
-
Serqiy... You really that old ?
--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) -
Hello John,
welcome to the forums! Just to avoid confusion to whom you are referring in your next posts: Ten Centuries and Veteran are "titles", based mainly on number of posts of the user. User name appears above that, in the area where your posts display "John Tate".
-
-
I try to use my memory... but I forget...
--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) -
They tell me taking Ginko Baloba helps with the memory problems. I keep forgetting to take mine!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply