January 17, 2006 at 9:32 am
I have a udf which was being called from a select statement which was returning a csv list of urls which were being displayed in a report
SELECT
@csv = COALESCE(@csv + ',','')+CAST(s.ReferenceNo as varchar(15))
FROM
tblStatement as s
JOIN
tblStatement_ResourceCodes as src
ON
s.StatementPK = src.StatementFK
WHERE
s.PersonnelFK = @PersonnelFK AND
s.DevNeeds = @DevNeeds AND
src.ResourceFK = @ResourceFK AND
s.Stamp > @StartDate AND
s.Stamp <= @EndDate AND
s.status = 1 AND
s.StatementPK in(Select s.StatementPK
from tblStatement as s
join tblStatement_Activities as sa
on s.statementPK = sa.StatementFK
where sa.ActivityFK in (select value from dbo.udf_split(@Activities,','))
)
which was working fine until customer has changed the format of the Reference code from 23.4 to just the right part after the .
so 4 or whatever (numeric from 1 to 10000) the left part is the PK of the client the right part is the number of the statement.
I want a udf or a regular expression (which I hear they cant do in 2000) so that I can replace all instances of this Reference Code with the new format. That bit is easy its just trying to find all of them in a string which could contain one or a hundred links.
eg:
12.34,href="/pages/admin/activitystatement_saved.asp?id=156">12.35,href="/pages/admin/activitystatement_saved.asp?id=124">12.36
I just want to pass in the string to a udf that finds all
occurances of > then anything that matches the pattern of numeric then . then numeric then < and replaces that with the right hand
part of the numeric reference code.
I could write some mean horrible thing that searches the whole string using patindexes but I don't know if anyone has any better idea.
Thanks in advance for any help.
January 17, 2006 at 1:32 pm
I say pay the price for the design that doesn't anticipate changes like this.
But why do complex search and replace when you can do it only once?
Why not do the replace once (using CASE) in the coalesce statement? Isn't the number part isolated there or always at the end of the string?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply