[Solved] TRIM multiple characters from a string

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

[Solved] TRIM multiple characters from a string

Post 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
Last edited by dreamquartz on Sun Feb 03, 2019 7:25 pm, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: TRIM multiple characters from a string

Post 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)
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [SOLVED] TRIM multiple characters from a string

Post by dreamquartz »

Added a separate record to the table for this problem.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] TRIM multiple characters from a string

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] TRIM multiple characters from a string

Post 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

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

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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] TRIM multiple characters from a string

Post by eremmel »

Good, you improved the normalization of the datamodel!
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply