Page 1 of 1

[Solved] TRIM multiple characters from a string

PostPosted: Sat Jan 19, 2019 8:44 am
by dreamquartz
Hi All,

Looking for a method to trim multiple characters from in a string, when communicating with certain Clients.

The record shows e.g. 'Document A-Renew.'.
There is an other record that shows 'Document A'
Both have the same Qualification.

There are also other documents that end on ' BiG or '-small', which can have their own Qualification.

When our Client is communicating with their Clients in relationship to the same Qualification, they want to inform them that their 'Document A' needs to be renewed.
Because both the mentioned Documents have the same Qualification, our Client only want to communicate about 'Document A'.

I am trying therefore to TRIM the record 'Document A-Renew' to just "Document A' and e.g. other documents from Ŕecord-small' to 'Record'.
TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim character> ] FROM ] <char value expr> )
would be a principle, but that only trims one character.
I can get the trailing characters, like '-Renew', but I have no idea how to get just the left part of the string, which varies in length.

Any suggestions?

Kind regards,

Dream

Re: TRIM multiple characters from a string

PostPosted: Sat Jan 19, 2019 7:38 pm
by UnklDonald418
I have no idea how to get just the left part of the string

Perhaps one of these will help

LEFT
LEFT ( <char value expr>, <count> )
Returns a character string consisting of the first <count> characters of <char value expr>. Equivalent to SQL/
Foundation SUBSTRING(<char value expr> FROM 0 FOR <count>). (JDBC)

SUBSTR
{ SUBSTR | SUBSTRING } ( <char value expr>, <offset>, <length> )
The JDBC version of SQL/Foundation SUBSTRING returns a character string that consists of <length> characters
from <char value expr> starting at the <offset> position. (JDBC)

SUBSTRING
SUBSTRING ( <char value expr> FROM <start position> [ FOR <string length> ]
[ USING CHARACTERS ] )
SUBSTRING ( <binary value expr> FROM <start position> [ FOR <string length> ] )
The character version of SUBSTRING returns a character string that consists of the characters of the <char value
expr> from <start position>. If the optional <string length> is specified, only <string length>
characters are returned.
The binary version of SUBSTRING returns a binary string in the same manner. (Foundation)

Re: [SOLVED] TRIM multiple characters from a string

PostPosted: Sun Feb 03, 2019 7:25 pm
by dreamquartz
Added a separate record to the table for this problem.

Re: [Solved] TRIM multiple characters from a string

PostPosted: Thu Feb 07, 2019 1:17 am
by eremmel
Can you elaborate your found solution, so other might learn from this.
P.S.
Your problem description indicates that the data model is not well normalized.

Re: [Solved] TRIM multiple characters from a string

PostPosted: Sun Feb 10, 2019 6:14 am
by dreamquartz
Our Client requested additional functionality for their DataBase.
They wanted to inform Document owners that their documents needed to be renewed. Our Client stores two types of documents in their DataBase.
1. Documents that were initially released ( e.g. Document A) and
2. the Renewed version of the same document (e.g. Document A-Renew).
FWI You can compare these documents to the Driver license issued by the Dutch CBR.

Our Client initially put the onus on the Document owners to renew their own documents, but that did not really benefit the processes in their organization.
Our Client requested therefore a functionality that automates the process to inform Document owners to renew their documents.
The DataBase holds the metadata about these documents, including the date these documents were issued and the period of validity of those document. It was quickly understood that you cannot inform Document owners to renew a document that was already indicated as a renewal of their initial document. Because both renewed and initial issues documents carry the exact same weight, it was decided to just communicate about a "BasicDocument (e.g. Document A) to cover both the initial and the '-Renew'. Because the Table holding the metadata had a record type record that held both the information, 'A' and 'A-Renew', it became a challenge to trim '-Renew' from the one record. The solution became to add a new column to the record, holding just '-Renew' and the likes. Our Client now communicates to the Document owners using the "BasicDocument", while for other purposes the Query for "Document" is used. See below.
Code: Select all   Expand viewCollapse view
SELECT
    "tTypeOfDocument"."TypeOfDocument" ||
       COALESCE ( ' ' || "tTypeOfDocumentCoding"."TypeOfCertificateCoding", '' ) "BasicDocument"
FROM
    "tTypeOfDocument",
    {
       oj "tDocumentQualification"
          LEFT OUTER JOIN "tTypeOfDocumentCoding" ON
             "tDocumentQualification"."FKTypeOfDocumentCodingID" = "tTypeOfDocumentCoding"."TypeOfDocumentCodingID"
    }
WHERE
    "tDocumentQualification"."FKTypeOfDocumentID" = "tTypeOfDocument"."TypeOfDocumentID"

for the full document info:
Code: Select all   Expand viewCollapse view
SELECT
    "tTypeOfDocument"."TypeOfDocument" ||
       COALESCE ( ' ' || "tTypeOfDocumentCoding"."TypeOfCertificateCoding", '' ) ||
       COALESCE ( '-' || "tTypeOfDocumentCoding"."TypeOfDocumentCodingStatus", '' ) "Document"
FROM
    "tTypeOfDocument",
    {
       oj "tDocumentQualification"
          LEFT OUTER JOIN "tTypeOfDocumentCoding" ON
             "tDocumentQualification"."FKTypeOfDocumentCodingID" = "tTypeOfDocumentCoding"."TypeOfDocumentCodingID"
    }
WHERE
    "tDocumentQualification"."FKTypeOfDocumentID" = "tTypeOfDocument"."TypeOfDocumentID"

Hopes this explains our solution.

Dream

Re: [Solved] TRIM multiple characters from a string

PostPosted: Sun Feb 10, 2019 8:04 pm
by eremmel
Good, you improved the normalization of the datamodel!