I understand you would be able to query for records using a pattern match, but the REPLACE function itself when using it to replace, will replace exactly those characters or character that you use in its input parameters.
So after finding the record, you would still need to provide the whole ID to replace, and not just one character of it at a time as doing that will replace that character found anywhere in the whole notes field. I think turning this task over to the DBA's as you said you may in another response to this thread, is a better idea as they would be able to do it a lot more efficiently than an AR System code would be capable of, for doing something like this, even if we use a whole dataset of government ID's to compare against, whilst changing the underlying data. The other advantage of doing it using the DB would be that you would not impact the timestamp and userstamp of the notes entries. You might also want to look at how you would prevent a user from inputting government ID's into the notes field going forward or how to mask it if they do input it. I know that would mean trying to design the system to be idiot proof, and there is always an exceptional idiot who would still figure a way to sneak in an ID. So it might be more a case of enforcing a policy that users should not enter any government ID's in that field going forward. Just something to think about though. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Sinclair, Keith Sent: Wednesday, April 09, 2014 6:09 PM To: arslist@ARSLIST.ORG Subject: Re: Syntax needed for a filter to find a number string in HPD:Help Desk Notes field Joe, Misi, I am using the specific pattern of the ID, NNN-NN-NNNN as the key on the searches, as well as the key to the error handling so that I won't affect an IP or a phone number, etc. Essentially, the process I have implemented is this, in a nut shell: 3 fields on HPD:Help Desk - 1 integer ("A"), 2 character fields ("B" & "C"). Filter one fires off and does the following: "A" STRSTRC($Detailed Decription$, "-") - look for the first hyphen in the string, set character number "B" SUBSTRC($Detailed Decription$, $A$ + 3) look for a second hyphen two characters after finding first hyphen "C" SUBSTRC($Detailed Decription$, $A$ - 3, $A$ + 7) Puts down string found "B" LEFTC($B$, 1) trims it down to 1 character used in error checking Filter two fires off only if second hyphen entered in "B" is in the field as a literal character. Then performs: 'Detailed Decription (sic)' REPLACE($Detailed Decription$, $C$, "XXX-XX-XXXX") Still in work in progress but I want to see if I can fully flesh this out before giving up and handing it over to the DBA to have them do it as it could not only scrub past data but as people save tickets in the present. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Wednesday, April 09, 2014 3:48 PM To: arslist@ARSLIST.ORG Subject: Re: Syntax needed for a filter to find a number string in HPD:Help Desk Notes field Hi, If the format always match nnn-nn-nnnn, you can do some filter looping to mask it out without affecting performance too much. Start Filter: Run If: 'Notes' LIKE "%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" Set-Fields: tmp = $Notes$ Call Guide: Remove Ids Guide Remove Ids label: loop Filter Remove Id Run If: 'tmp' LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" Set-Fields: Notes = REPLACE($Notes$, LEFT($tmp$, 11), "xxx-xx-xxxx") Set-Fields: tmp = SUBSTR($tmp$, 11) Filter Next Number 0 Run If: 'tmp' LIKE "%0[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" Set-Fields: tmp = SUBSTR($tmp$, STRSTR($tmp$, "0")) Goto Guide Label: loop Filter Next Number 1 Run If: 'tmp' LIKE "%1[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" Set-Fields: tmp = SUBSTR($tmp$, STRSTR($tmp$, "1")) Goto Guide Label: loop ... Filter Next Number 9 Run If: 'tmp' LIKE "%9[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" Set-Fields: tmp = SUBSTR($tmp$, STRSTR($tmp$, "9")) Goto Guide Label: loop I confess that the 10 loop filter got a little more complex than I would have liked, but it seemed bad practice to step one character at a time forward. There is just no function to find the position of the first number... Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) Ask the Remedy Licensing Experts (Best R.O.I. Award at WWRUG10/11/12/13): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. > You could also use a single set field with a nested REPLACE instead of > 10 separate set field actions. > > Given your description of what you want to do, aren't you worried > though that it will replace even non Government ID kind of numerical > strings that might be important to your data? For eg, if there is > information about some IP address or contact information within the > notes that contain numbers, etc. will also get replaced thus you may > loose potentially important information too. > > An automated approach that may be more acceptable and perhaps a lot > more cleaner, is if you have an available database of Government ID's > which you can compare the notes field against to find a match, and > replace that ID if a match it found with a ***** string or maybe > **GovtID** to indicate the masked characters represent a Government ID. > > Or the old school expensive method of a manual cleanup instead of an > automated one, in case other numerical data within notes might be > critical to your business. > > Personally I would opt for the comparison method and replace the found > strng, assuming you do have an available and COMPLETE list of > Government ID's you wish to protect. > > Cheers > > Joe > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W > Sent: Wednesday, April 09, 2014 10:48 AM > To: arslist@ARSLIST.ORG > Subject: Re: Syntax needed for a filter to find a number string in > HPD:Help Desk Notes field > > Ah ... The Replace function in ARS is not a regex type replacement. It is > a straight character replacement. (The Run-If can look for patterns > like you are doing not the REPLACE function) > > What you can do is > Run-If: > 'Notes' LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" > > Set Fields > REPLACE($Detailed Description$, "0", "X") Set Fields REPLACE($Detailed > Description$, "1", "X") Set Fields REPLACE($Detailed Description$, > "2", "X") Set Fields REPLACE($Detailed Description$, "3", "X") Set > Fields REPLACE($Detailed Description$, "4", "X") Set Fields > REPLACE($Detailed Description$, "5", "X") Set Fields REPLACE($Detailed > Description$, "6", "X") Set Fields REPLACE($Detailed Description$, > "7", "X") Set Fields REPLACE($Detailed Description$, "8", "X") Set > Fields REPLACE($Detailed Description$, "9", "X") > > To replace it as a pattern you would have to do something different > (like using an SQL Set Fields action) (I'm an Oracle person so here it > would be in Oracle format) > SELECT REGEXP_REPLACE ('$Detailed Description$', > '[0-9]{3}-[0-9]{2}-[0-9]{4}', 'XXX-XX-XXXX') FROM DUAL > > Fred > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Sinclair, Keith > Sent: Wednesday, April 09, 2014 8:57 AM > To: arslist@ARSLIST.ORG > Subject: Re: Syntax needed for a filter to find a number string in > HPD:Help Desk Notes field > > Hi Misi, > > What I am trying to do is to have the REPLACE function find a number > in a filter, any number, in the 'Notes' field and replace it with an > X. We are trying to create a process that searches for Government ID > number strings entered into the system and replace it with "X"s to > remove any personally identifiable information. > > The problem lies in trying to get the "[" and "]" characters to be > seen as breakout characters by MS SQL at the Application/DB level. The > user tool handles this without issue, but the workflow handles this differently. > > Hope this helps clarify. > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky > Sent: Wednesday, April 09, 2014 3:21 AM > To: arslist@ARSLIST.ORG > Subject: Re: Syntax needed for a filter to find a number string in > HPD:Help Desk Notes field > > Hi, > > It seems like you have made this over complex there is no need to add > things together. > > The original advanced search should be: > 'Notes' LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%" > > This replace string would equal what you have: > REPLACE($Detailed Description$, "[0-9]", "XX") > > If you want the filter to match use the leading match in the advanced > search example, it would be enough to use it as it is. > > Run If: ('Notes' LIKE > "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%") > or > Set-Fields If: ('Notes' LIKE > "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%") > > What the REPLACE does is to change the literal string "[0-9]" to "XX". > It does not make sense to me. > > I presume you want to replace the leading string "012-34-5678" with > something else??? In that case you could use something like the > following that first strips the 11 characters and adds the new prefix XX: > "XX" + SUBSTR($Field$, 11) > > Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP > 2011) > > Ask the Remedy Licensing Experts (Best R.O.I. Award at WWRUG10/11/12/13): > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > Find these products, and many free tools and utilities, at http://rrr.se. > >> >> Hi Listers, >> >> Just as the subject line describes it, I am trying to replicate a >> search performed in the User Tool: >> >> 'Notes' LIKE "[0-9][0-9][0-9]" + "-" + "[0-9][0-9]" + "-" + >> "[0-9][0-9][0-9][0-9]%", which returns records with a matching number >> string successfully. >> >> However, I need to use the same search in a filter that is using the >> REPLACE() function. >> >> I believe the problem lies with the way MS SQL handles the "[" and "]" >> characters. >> >> My Replace FUNCTION is currently: >> >> REPLACE($Detailed Decription$, ((("[" + "0") + "-") + "9") + "\]", >> "XX") >> >> I know it's probably something very simple that's not correct but I >> cannot seem to get this to work. >> >> Anyone know what the proper syntax would be to get this to work with >> number strings? >> >> Specs: >> Windows Server 2008 R2 >> ARS 7.6.3sp3 >> ITSM 7.6.3 >> MS SQL 2008 >> >> Thanks, >> >> Keith Sinclair >> Sr. Application Developer >> >> DeVry Education Group >> 814 Commerce Dr. >> Oakbrook, IL 60523-8822 >> p: 630.645.1143 >> e: ksincl...@devry.com<mailto:ksincl...@devry.com> >> w: www.devryeducationgroup.com<http://www.devryeducationgroup.com> > > > > ______________________________________________________________________ > ______ > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the > Answers Are, and have been for 20 years" > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > "Where the Answers Are, and have been for 20 years" > ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years" ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
