Hi all I need a little help on a problem I have. My client has a sql db that is populated by (muppets) they have a free txt field that is set to varchar(8000) and they should type in there what they have fixed for the person calling but they don't they type in word and copy and paste so we are left with a load of strange characters to remove. I have been able to remove all from the body of the txt but have a problem with the leading txt. My issue is there is two points it can start from and I need to do this in one code any help would be grateful.
It would help if you would post a snippet or example of your input data and along with what it should look like once corrected
I'd love to but the data is sensitive
You can obfuscate the data and post that, or just make up your own bogus sample data that is representative of the problem that you want to solve.
I read your posting couple of times, but still was not able to follow the issue you are trying to resolve.
I have removed all personal details but it will not allow me to upload the code as it mentions two or more names I will try and upload it separate.
Table build
DECLARE @ReplaceTest Table
(CUST_SiteVisitDetails varchar (8000),CUSTSiteVisitDetails varchar (8000))
INSERT INTO @ReplaceTest
SELECT '{\rtf1\ansi\ansicpg1252\deff0\deflang2057{\fonttbl{\f0\fnil\fcharset0 Arial;}} \viewkind4\uc1\pard\fs23 Demonstration of MS Lync to the Sustainable Development Group. Demo went well, interesting in using Lync as a communications tool for the Acadamy to deliver training. waiting for new finaical year to see if the group would like to invest in licences\par } '
, '{\f1 Calibri{\f2 Tahoma{\f3 Arial;\red31\green73\blue125 {*\listtable {\list\listtemplateid-1\listhybrid{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807567 \02\00.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-360\li720\lin720{\listlevel\levelnfc4\levelnfcn4\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807577 \02\01.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-360\li1440\lin1440{\listlevel\levelnfc2\levelnfcn2\leveljc2\leveljcn2\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807579 \02\02.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-180\li2160\lin2160{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807567 \02\03.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-360\li2880\lin2880{\listlevel\levelnfc4\levelnfcn4\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807577 \02\04.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-360\li3600\lin3600{\listlevel\levelnfc2\levelnfcn2\leveljc2\leveljcn2\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807579 \02\05.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-180\li4320\lin4320{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807567 \02\06.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-360\li5040\lin5040{\listlevel\levelnfc4\levelnfcn4\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807577 \02\07.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-360\li5760\lin5760{\listlevel\levelnfc2\levelnfcn2\leveljc2\leveljcn2\levelfollow0\levelstartat1{\leveltext\leveltemplateid134807579 \02\08.{\levelnumbers\01\levellegal0\levelnorestart0\cf0\fi-180\li6480\lin6480{\listname \listid109471913 {\listoverride\listid109471913\listoverridecount0\ls1{\s1\sbasedon0\snext1\ql\li720\lin720\f122\cf0 List Paragraph{*\cs2\f220\cf0 Default Paragraph Font{*\cs3\sbasedon2\cf0 Line Number{*\cs4\ul\cf1 Hyperlink\sectd\plain\ql\outlinelevel0{\b\f322\cf0 From:{\f322\cf0 Wright Cheryl \line {\b\f322\cf0 Sent:{\f322\cf0 11 February 2015 09:58\line {\b\f322\cf0 To:{\f322\cf0 \line {\b\f322\cf0 Cc:{\f322\cf0\line {\b\f322\cf0 Subject:{\f322\cf0 SystmOne down time\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 Hi\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 We telephoned to report systmone down across the whole practice at approx. 910 this morning\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 The call took 15 \u8211\96 20 mins to be answered and then another 20 min to be dealt with.~ The call was disconnected at this time (not sure how or why) and we attempted to call back and were reconnected with the person (unnamed female) we had spoken to before. I am unable to give you a reference number for this call as we have still not received the email.\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 Before we logged the call we did the usual standard checks of logging out of all of the pcs and re-booting them \u8211\96 we do this every time we lose the system prior to making the call to the helpdesk\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 This morning we have been without a system for over 40mins with no explanation of why it happened, if you could investigate and give me some kind of explanation I would appreciate it.\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 Thanks\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 regards\f322\par\plain\ql\f322\par\plain\ql{\f322\cf0\f322\par\plain\ql{\f322\cf0 Office Manager\f322\par\plain\ql{\f322\cf0 \f322\par\plain\ql\f322\par\plain\ql{\f322\cf0 Response {\f322\cf0 from LHIS\f322\par\plain\ql{\f322\cf2 Hi ,\f322\cf2\par\plain\ql\f322\cf2\par\plain\ql{\f322\cf2 First I would like to apologise for the inconvenience and level of service which you received. I have done some investigation on this call and please find below my response to your queries.\f322\cf2\par\plain\ql\f322\cf2\par{\listtext 1.\tab \plain\ilvl0\ls1\s1\ql\fi-360\li720\lin720{\f322\cf2 SystmOne is supported/maintained by HSCIC and I have checked on their service website and there are no reported issues. We haven\u8217\92t had any issues reported to us by other GP sites so it seems like it could be an issue just at your site however I am unable to comment on the specific reason for the outage.\f322\cf2\par{\listtext 2.\tab \plain\ilvl0\ls1\s1\ql\fi-360\li720\lin720{\f322\cf2 I checked Service Desk waiting and call handling times during the period of your call and the maximum average waiting time was 6mins. Please see below stats from the system report. \f322\cf2\par{\listtext 3.\tab \plain\ilvl0\ls1\s1\ql\fi-360\li720\lin720{\f322\cf2 I have spoken to (Service Desk engineer who dealt with your call) and she advised that she tried to call you back when the call got disconnected however your line was engaged. In the meantime you contacted Service desk and you were put through to her. With regards to the checks which we carried out are part of the basic troubleshooting which we have to follow to understand the issue although I agree that it can be sometime frustrating especially if you have already restarted your PC before contacting Service Desk. Kajal did advise that when she was troubleshooting the system started to work ok and your staff was able to log in without any issues.\f322\cf2\par\plain\ql\f322\cf2\par\plain\ql{\f322\cf2 I am not sure if you are aware but I would recommend using {\b\f322\cf2 Option 9{\f322\cf2 when calling x3500 to report site/clinical system outages. Any calls to this option don\u8217\92t have to wait in the normal service desk queue and are answered directly by 2{\super\f322\cf2 nd{\f322\cf2 line engineers. This option should only be used when your entire site or clinical system is down.\f322\cf2\par\plain\ql\f322\cf2\par\plain\ql{\f322\cf2 I hope this helps but if you would like to discuss this further, please contact me on the below number.\f322\cf2\par\plain\ql\f322\cf2\par\plain\ql{\f322\cf2 Kind Regards\f322\cf2\par\plain\ql\f322\cf2\par\plain\ql{\b\f322\cf2\b\f322\cf2\par\plain\ql{\f322\cf2 First Line Support Manager\f322\par'
1 Like
The issue I have is with the charindex
SUBSTRING([CUST_SiteVisitDetails],
CHARINDEX(';}',[CUST_SiteVisitDetails]), LEN([CUST_SiteVisitDetails])), ';}', '')
It is looking for ;} in Cust_SiteVisitDetails and when I change it to 'From:{\f322\cf0 ' from CUSTSiteVisitDetails that works but the data that is in the Variable table is normally one column. so I need both to work in one SUBSTRING
RTF is a nightmare ... you can't just strip out the stuff between { and } because sometimes it contains embedded characters, not just formatting - its optimised to include text within the {xxx} to save having multiple sets of {\formatting} tags.
Here is a thread that has a go at removing RTF
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90034
I would put a trigger on the table and rollback any attempt to store what looks like RTF in the record. That will take care of future data (presumably the user's browser has a Paste-as-plain-text option, if not there are Browser TextBox controls that let you do that (and, I think, can preserve formatting in a way that HTML can view them, if you actually want that)