Controlled truncation of a data string

greg

In supply and distribution application I am developing, I store product catalogue numbers and then allow users to search and to re-use products already entered on the database. A problem was identified with longer catalogue numbers that users tend to enter the same number in different ways by inserting spaces, dashes, dots, etc. Moreover, a supplier company sometimes changes presentation of their catalogue numbers in a new catalogue.

A way around this problem would be to have a function which compresses a text string by removing certain characters from it, i.e. converting 12.34 56-7 to 1234567 and to store the catalogue number as it was entered by a user in one attribute and in a truncated form in another atribute. Then a search can be performed though the second attribute.

Any thoughts?

greg

aware_support

You can use the "REPLACE" function to replace the punctuation characters that you specify with an empty string. To be able to use this solution for your problem you will need to do the following:
a) Get the product to store the catalog number in two formats - as user enters it and as it should be searched. So Product will need to have the additional calculated attribute to calculate and store the "good" number (the new "replace" function will do the calculation).
b) The query that searches for products should take user input, use the "REPLACE" function to calculate the "good" number and then use it to compare with the "good" attribute in the product. In order to do this you will need to collect query input in a temporary object and then run query using the attribute of this object

Example of REPLACE function usage:
REPLACE (Product.Catalogue, '[,-.]', '')
Second parameter is a regular expression. Any characters specified inside the square brackets will be replaced.

Aware IM Support Team

greg

Can I remove an empty space with the REPLACE function? greg

aware_support wrote
You can use the "REPLACE" function to replace the punctuation characters that you specify with an empty string. To be able to use this solution for your problem you will need to do the following:
a) Get the product to store the catalog number in two formats - as user enters it and as it should be searched. So Product will need to have the additional calculated attribute to calculate and store the "good" number (the new "replace" function will do the calculation).
b) The query that searches for products should take user input, use the "REPLACE" function to calculate the "good" number and then use it to compare with the "good" attribute in the product. In order to do this you will need to collect query input in a temporary object and then run query using the attribute of this object

Example of REPLACE function usage:
REPLACE (Product.Catalogue, '[,-.]', '')
Second parameter is a regular expression. Any characters specified inside the square brackets will be replaced.

Aware IM Support Team

aware_support

Yes, you can.

In fact, REPLACE function is very powerful - it fully supports regular expressions.

greg

Is it a way to use the REPLACE function to replace all capital letters with low case?

aware_support

You can use TO_LOWER_CASE and TO_UPPER_CASE functions for that - it is much simpler that way.

greg

In my configuration, I want to produce a shorten version of a product catalogue number. I have an attribute CatNo, I introduced another calculated attribute CatNoShort and a rule like this one

Product.CatNoShort=Product.CatNo
REPLACE (Product.CatNoShort, '[ ,.]', '')

The system did not allow the second line of this rule. The message was that an identifier was expected in column 9, i.e. where the first bracket is. It seems that the REPLACE function is treated as an action to replace an instance of a business object, not a text string. Can you help?

aware_support

My apologies regarding the name of the function. It is called REPLACE_PATTERN, not REPLACE and is a function, not an action.

Therefore the correct usage of this function in your case is:

Product.CatNoShort = REPLACE_PATTERN (Product.CatNoShort, '[ ,.]', '')

greg

Thank you. Can you confirm that in this case this function will replace/remove not the full string '[ ,-.]' but any one of these symbols?

aware_support

Yes, this function will replace all characters specified inside square brackets. See also partial description of regular expressions below.

Summary of regular-expression constructs
Construct Matches

Characters
x The character x
\ The backslash character
\0n The character with octal value 0n (0 <= n <= 7)
\0nn The character with octal value 0nn (0 <= n <= 7)
\0mnn The character with octal value 0mnn (0 <= m <= 3, 0 <= n <= 7)
\xhh The character with hexadecimal value 0xhh
\uhhhh The character with hexadecimal value 0xhhhh
\t The tab character ('\u0009')
\n The newline (line feed) character ('\u000A')
\r The carriage-return character ('\u000D')
\f The form-feed character ('\u000C')
\a The alert (bell) character ('\u0007')
\e The escape character ('\u001B')
\cx The control character corresponding to x

Character classes
[abc] a, b, or c (simple class)
[^abc] Any character except a, b, or c (negation)
[a-zA-Z] a through z or A through Z, inclusive (range)
[a-d[m-p]] a through d, or m through p: [a-dm-p] (union)
[a-z&&[def]] d, e, or f (intersection)
[a-z&&[^bc]] a through z, except for b and c: [ad-z] (subtraction)
[a-z&&[^m-p]] a through z, and not m through p: a-lq-z

Predefined character classes
. Any character (may or may not match line terminators)
\d A digit: [0-9]
\D A non-digit: [⁰-9]
\s A whitespace character: [ \t\n\x0B\f\r]
\S A non-whitespace character: [^\s]
\w A word character: [a-zA-Z_0-9]
\W A non-word character: [^\

pixerp

I found this (old) post in relation to my requirement:

BO Address has attributes Name, Street, City and FullAddr. A BO rule should take care to fulfill the FullAddr attribute if empty, ie.

If Address.FullAddr IS UNDEFINED Then
Address.FullAddr=Address.Name+\n+Address.Street+\n+Address.City

After trying with ' ', '<<>>', '/\n/' and...., I just can't get a newline code to be inserted between each attributes .

How should it be done ?
Thanks