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.
| Author |
Topic |
|
pamit_1982
Starting Member
13 Posts |
 Posted - 2007-06-21 : 10:40:11
|
| I have a table where in there are numerous text messages.I have a function done which separates a particular value from the function.But this function fails for a particular message.I have to add a separate condition only for that message Please advice...please find below the function and the textmessages..The function fails for below 2 messagesSELECT CaseID --transform the substring to an integer in order to use it in a calculation , notetext , case when notetext like '%unable to process%' then 0 when notetext like '%bounced%' then -1 else 1 end as FeeSign , cast( cast( case when case --determine which final character to use as the string terminator when (letter <> 0 and letter < space) then letter when space = 0 then 99 else space end = 99 then '' when case --determine which final character to use as the string terminator when (letter <> 0 and letter < space) then letter when space = 0 then 99 else space end = 0 then '' else substring(notetext, pound+1, (case --determine which final character to use as the string terminator when (letter <> 0 and letter < space) then letter when space = 0 then 99 else space end)-1 ) end as float) as int) as AmountPaid FROM (--get fund app fee cases and key string position select CaseID , NoteText , charindex('£', notetext) as 'Pound' , charindex('.', substring(notetext, charindex('£', notetext)+1, 50)) as 'FullStop' , charindex(' ', substring(notetext, charindex('£', notetext)+1, 50)) as 'Space' , patindex('%[a-z,A_Z,-]%', substring(notetext, charindex('£', notetext)+1, 50)) as 'Letter' from tblcasenotespost2003 where notetext like 'fund app fee%' and notetext like '%£%' ) as AmtPaidNotesfund app fee - unable to process £285 streamline as not authorised.emailed to advise.fund app fee - unable to process £505 streamline as not auth.email sent to advisefund app fee - unable to process streamline for £110 as not auth.emaile sent to advise.Fund app fee - sline processed for £285 auth code 011511Fund app fee - sline processed for £120 auth code 059402fund app fee - refund of £165 processed today.auth code 000000fund app fee - refund of £195 processed today.auth code 6122FUND APP FEE - chq recd £75.00 - banked 21.6.7.FUND APP FEE - Chq recd £120.00 - admin fee - banked 21.6.7.fund app fee - unable to process streamline for £165 as NOT AUTH.emailed n fox,t taggart to advise.fund app fee - streamline for £295 processed today.auth code 285395fund app fee - streamline for £315 processed today.auth code 5394FUND APP FEE - chq recd £315.00 - admin/val fee - banked 21.6.7.Fund App Fee - App fee processed today for £315.00. Auth code 091058Fund App Fee - App fee processed today for £315.00. Auth code 074591. |
|
|
|
|
|
|
|
Text messages...