[Solved] TRIM multiple characters from a string

Discuss the database features

[Solved] TRIM multiple characters from a string

Postby dreamquartz » Sat Jan 19, 2019 8:44 am

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
Last edited by dreamquartz on Sun Feb 03, 2019 7:25 pm, edited 1 time in total.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

Re: TRIM multiple characters from a string

Postby UnklDonald418 » Sat Jan 19, 2019 7:38 pm

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)
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1213
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [SOLVED] TRIM multiple characters from a string

Postby dreamquartz » Sun Feb 03, 2019 7:25 pm

Added a separate record to the table for this problem.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] TRIM multiple characters from a string

Postby eremmel » Thu Feb 07, 2019 1:17 am

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] TRIM multiple characters from a string

Postby dreamquartz » Sun Feb 10, 2019 6:14 am

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
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] TRIM multiple characters from a string

Postby eremmel » Sun Feb 10, 2019 8:04 pm

Good, you improved the normalization of the datamodel!
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests