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:[email protected]] On Behalf Of Grooms, Frederick W
> Sent: Wednesday, April 09, 2014 10:48 AM
> To: [email protected]
> 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:[email protected]] On Behalf Of Sinclair, Keith
> Sent: Wednesday, April 09, 2014 8:57 AM
> To: [email protected]
> 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:[email protected]] On Behalf Of Misi Mladoniczky
> Sent: Wednesday, April 09, 2014 3:21 AM
> To: [email protected]
> 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: [email protected]<mailto:[email protected]>
>> 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"
