SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Removing Space from string

sandeepgupta_18Aug 17 2020 — edited May 16 2022

Hi All ,

I was looking for a help

I have a table city

in this table there is column "City_name"

that contains value like

City_name

RUTLAND

Pendleton

SIERRA

RANDOLPH

YANCEY

PATRICK

DENVER

CALDWELL

SCOTLAND

KEARNEY

PASCO

CATAWBA

LINN

FORT BEND

COLLIN

RIO ARRIBA

Most of these values contains spaces & New line character

to remove those i used upper(REGEXP_REPLACE(city_name, '[^0-9A-Za-z]', ''))

this worked for every city except where city name like "RIO ARRIBA" this removed the space between RIO and ARRIBA .

is there any way by which we could removed space, and new line character from the last and starting only if there is a space between string that should remain same..

thanks in advance

2734595

If there is a better place than "BPEL" to post this type of question please let me know. Thanks.

Martien van den Akker

How does your transformation look like. Is the column/element filled conditionally?

Regards,
Martien

2734595

Hey Martien ... all data types are being converted to a String, including the "FILE_CRT_DT" (protecteddateattribute2) column where this issue seems to start. This is being done in the Transform via String Conversion Function. All columns that are 'INT' and 'DATE' data types are being converted to a 'STRING' in the Transform.

Does that answer your question? If so, please let me know if you see a problem with the approach taken.

Screenshot of Transform Design:

Martien van den Akker

Can you show the part in the transform that fills the elements shown in the file? I don't see how the shown elements relate to the columns in the file. Is it a CSV file?

Make sure that the element is created even if the source element is empty. What I meant was if there was an xsl:if or xsl:choose construct around that element.

Regards,
Martien

2734595

Thanks for trying to help Martien.

Yes the file Write_Adapter generates .CSV file. Column names are updated manually after xsd creation according to this doc- Oracle SOA, BPEL,BPM: Generating .csv file using FileAdapter in Oracle SOA 11g

Elements renamed in CSV Header file:

Below is 'xsl' section of Transform source code that correlates to screenshot above**:**

<xsl:template match="/">

\<imp1:Root-Element>

  \<xsl:for-each select="/top:WftaskCollection/top:Wftask">

    \<imp1:single-record>

      \<imp1:PROTECTEDNUMBERATTRIBUTE3>

        \<xsl:value-of select="string(top:protectednumberattribute3)"/>

      \</imp1:PROTECTEDNUMBERATTRIBUTE3>

      \<imp1:PROTECTEDTEXTATTRIBUTE14>

        \<xsl:value-of select="top:protectedtextattribute14"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE14>

      \<imp1:PROTECTEDTEXTATTRIBUTE9>

        \<xsl:value-of select="top:protectedtextattribute9"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE9>

      \<imp1:PROTECTEDTEXTATTRIBUTE10>

        \<xsl:value-of select="top:protectedtextattribute10"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE10>

      \<imp1:PROTECTEDDATEATTRIBUTE2>

        \<xsl:value-of select="string(top:protecteddateattribute2)"/>

      \</imp1:PROTECTEDDATEATTRIBUTE2>

      \<imp1:PROTECTEDDATEATTRIBUTE1>

        \<xsl:value-of select="string(top:protecteddateattribute1)"/>

      \</imp1:PROTECTEDDATEATTRIBUTE1>

      \<imp1:UPDATEDDATE>

        \<xsl:value-of select="string(top:updateddate)"/>

      \</imp1:UPDATEDDATE>

      \<imp1:PROTECTEDTEXTATTRIBUTE12>

        \<xsl:value-of select="top:protectedtextattribute12"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE12>

      \<imp1:PROTECTEDTEXTATTRIBUTE4>

        \<xsl:value-of select="top:protectedtextattribute4"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE4>

      \<imp1:PROTECTEDNUMBERATTRIBUTE1>

        \<xsl:value-of select="string(top:protectednumberattribute1)"/>

      \</imp1:PROTECTEDNUMBERATTRIBUTE1>

      \<imp1:PROTECTEDTEXTATTRIBUTE3>

        \<xsl:value-of select="top:protectedtextattribute3"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE3>

      \<imp1:PROTECTEDDATEATTRIBUTE4>

        \<xsl:value-of select="string(top:protecteddateattribute4)"/>

      \</imp1:PROTECTEDDATEATTRIBUTE4>

      \<imp1:PROTECTEDTEXTATTRIBUTE7>

        \<xsl:value-of select="top:protectedtextattribute7"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE7>

      \<imp1:PROTECTEDTEXTATTRIBUTE11>

        \<xsl:value-of select="top:protectedtextattribute11"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE11>

      \<imp1:PROTECTEDTEXTATTRIBUTE6>

        \<xsl:value-of select="top:protectedtextattribute6"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE6>

      \<imp1:PROTECTEDTEXTATTRIBUTE5>

        \<xsl:value-of select="top:protectedtextattribute5"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE5>

      \<imp1:PROTECTEDTEXTATTRIBUTE1>

        \<xsl:value-of select="top:protectedtextattribute1"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE1>

      \<imp1:PROTECTEDTEXTATTRIBUTE8>

        \<xsl:value-of select="top:protectedtextattribute8"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE8>

    \</imp1:single-record>

  \</xsl:for-each>

\</imp1:Root-Element>

</xsl:template>

</xsl:stylesheet>

2734595

Transform updated to use Normalized function. The idea is to remove whitespace or junk characters, which could cause CSV file output issues:

<xsl:stylesheet version="1.0"

            xmlns:imp1="[http://TargetNamespace.com/Write_UmRmViewReport](http://TargetNamespace.com/Write_UmRmViewReport)"

            xmlns:xp20="[http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20](http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20)"

            xmlns:bpws="[http://schemas.xmlsoap.org/ws/2003/03/business-process/](http://schemas.xmlsoap.org/ws/2003/03/business-process/)"

            xmlns:bpel="[http://docs.oasis-open.org/wsbpel/2.0/process/executable](http://docs.oasis-open.org/wsbpel/2.0/process/executable)"

            xmlns:xsi="[http://www.w3.org/2001/XMLSchema-instance](http://www.w3.org/2001/XMLSchema-instance)"

            xmlns:bpm="[http://xmlns.oracle.com/bpmn20/extensions](http://xmlns.oracle.com/bpmn20/extensions)"

            xmlns:tns="[http://xmlns.oracle.com/pcbpel/adapter/db/UmRmViewReport_master_updated/UmRmViewReport/Read_UmRmViewReport](http://xmlns.oracle.com/pcbpel/adapter/db/UmRmViewReport_master_updated/UmRmViewReport/Read_UmRmViewReport)"

            xmlns:plt="[http://schemas.xmlsoap.org/ws/2003/05/partner-link/](http://schemas.xmlsoap.org/ws/2003/05/partner-link/)"

            xmlns:ns0="[http://xmlns.oracle.com/pcbpel/adapter/file/UmRmViewReport_master_updated/UmRmViewReport/Write_UmRmViewReport](http://xmlns.oracle.com/pcbpel/adapter/file/UmRmViewReport_master_updated/UmRmViewReport/Write_UmRmViewReport)"

            xmlns:wsdl="[http://schemas.xmlsoap.org/wsdl/](http://schemas.xmlsoap.org/wsdl/)"

            xmlns:ora="[http://schemas.oracle.com/xpath/extension](http://schemas.oracle.com/xpath/extension)"

            xmlns:socket="[http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator](http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator)"

            xmlns:mhdr="[http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction](http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction)"

            xmlns:oraext="[http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc](http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc)"

            xmlns:dvm="[http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue](http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue)"

            xmlns:hwf="[http://xmlns.oracle.com/bpel/workflow/xpath](http://xmlns.oracle.com/bpel/workflow/xpath)"

            xmlns:med="[http://schemas.oracle.com/mediator/xpath](http://schemas.oracle.com/mediator/xpath)"

            xmlns:xsl="[http://www.w3.org/1999/XSL/Transform](http://www.w3.org/1999/XSL/Transform)"

            xmlns:jca="[http://xmlns.oracle.com/pcbpel/wsdl/jca/](http://xmlns.oracle.com/pcbpel/wsdl/jca/)"

            xmlns:ids="[http://xmlns.oracle.com/bpel/services/IdentityService/xpath](http://xmlns.oracle.com/bpel/services/IdentityService/xpath)"

            xmlns:xdk="[http://schemas.oracle.com/bpel/extension/xpath/function/xdk](http://schemas.oracle.com/bpel/extension/xpath/function/xdk)"

            xmlns:xref="[http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions](http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions)"

            xmlns:xsd="[http://www.w3.org/2001/XMLSchema](http://www.w3.org/2001/XMLSchema)"

            xmlns:bpmn="[http://schemas.oracle.com/bpm/xpath](http://schemas.oracle.com/bpm/xpath)"

            xmlns:top="[http://xmlns.oracle.com/pcbpel/adapter/db/top/Read_UmRmViewReport](http://xmlns.oracle.com/pcbpel/adapter/db/top/Read_UmRmViewReport)"

            xmlns:ldap="[http://schemas.oracle.com/xpath/extension/ldap](http://schemas.oracle.com/xpath/extension/ldap)"

            exclude-result-prefixes="xsi xsl tns plt wsdl xsd top imp1 ns0 jca xp20 bpws bpel bpm ora socket mhdr oraext dvm hwf med ids xdk xref bpmn ldap">

<xsl:template match="/">

\<imp1:Root-Element>

  \<xsl:for-each select="/top:WftaskCollection/top:Wftask">

    \<imp1:single-record>

      \<imp1:PROTECTEDNUMBERATTRIBUTE3>

        \<xsl:value-of select="string(normalize-space(top:protectednumberattribute3))"/>

      \</imp1:PROTECTEDNUMBERATTRIBUTE3>

      \<imp1:PROTECTEDTEXTATTRIBUTE14>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute14)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE14>

      \<imp1:PROTECTEDTEXTATTRIBUTE9>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute9)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE9>

      \<imp1:PROTECTEDTEXTATTRIBUTE10>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute10)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE10>

      \<imp1:PROTECTEDDATEATTRIBUTE2>

        \<xsl:value-of select="string(normalize-space(top:protecteddateattribute2))"/>

      \</imp1:PROTECTEDDATEATTRIBUTE2>

      \<imp1:PROTECTEDDATEATTRIBUTE1>

        \<xsl:value-of select="string(normalize-space(top:protecteddateattribute1))"/>

      \</imp1:PROTECTEDDATEATTRIBUTE1>

      \<imp1:UPDATEDDATE>

        \<xsl:value-of select="string(normalize-space(top:updateddate))"/>

      \</imp1:UPDATEDDATE>

      \<imp1:PROTECTEDTEXTATTRIBUTE12>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute12)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE12>

      \<imp1:PROTECTEDTEXTATTRIBUTE4>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute4)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE4>

      \<imp1:PROTECTEDNUMBERATTRIBUTE1>

        \<xsl:value-of select="string(normalize-space(top:protectednumberattribute1))"/>

      \</imp1:PROTECTEDNUMBERATTRIBUTE1>

      \<imp1:PROTECTEDTEXTATTRIBUTE3>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute3)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE3>

      \<imp1:PROTECTEDDATEATTRIBUTE4>

        \<xsl:value-of select="string(normalize-space(top:protecteddateattribute4))"/>

      \</imp1:PROTECTEDDATEATTRIBUTE4>

      \<imp1:PROTECTEDTEXTATTRIBUTE7>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute7)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE7>

      \<imp1:PROTECTEDTEXTATTRIBUTE11>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute11)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE11>

      \<imp1:PROTECTEDTEXTATTRIBUTE6>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute6)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE6>

      \<imp1:PROTECTEDTEXTATTRIBUTE5>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute5)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE5>

      \<imp1:PROTECTEDTEXTATTRIBUTE1>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute1)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE1>

      \<imp1:PROTECTEDTEXTATTRIBUTE8>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute8)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE8>

    \</imp1:single-record>

  \</xsl:for-each>

\</imp1:Root-Element>

</xsl:template>

</xsl:stylesheet>

=======================================================================================================================================

Two (2) more attempts to fix Transform logic below.

Solution #1 using 'IF Test = String-Length' logic on first column:

<xsl:template match="/">

\<imp1:Root-Element>

  \<xsl:for-each select="/top:WftaskCollection/top:Wftask">

    \<imp1:single-record>

      \<imp1:PROTECTEDNUMBERATTRIBUTE3>

      **\<xsl:if test = "string-length(normalize-space(top:protectednumberattribute3))>0">**

            **\<xsl:value-of select="string(normalize-space(top:protectednumberattribute3))"/>**

      **\</xsl:if>**

      \</imp1:PROTECTEDNUMBERATTRIBUTE3>

      \<imp1:PROTECTEDTEXTATTRIBUTE14>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute14)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE14>

      \<imp1:PROTECTEDTEXTATTRIBUTE9>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute9)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE9>

      \<imp1:PROTECTEDTEXTATTRIBUTE10>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute10)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE10>

      \<imp1:PROTECTEDDATEATTRIBUTE2>

        \<xsl:value-of select="string(normalize-space(top:protecteddateattribute2))"/>

      \</imp1:PROTECTEDDATEATTRIBUTE2>

      \<imp1:PROTECTEDDATEATTRIBUTE1>

        \<xsl:value-of select="string(normalize-space(top:protecteddateattribute1))"/>

      \</imp1:PROTECTEDDATEATTRIBUTE1>

      \<imp1:UPDATEDDATE>

        \<xsl:value-of select="string(normalize-space(top:updateddate))"/>

      \</imp1:UPDATEDDATE>

      \<imp1:PROTECTEDTEXTATTRIBUTE12>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute12)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE12>

      \<imp1:PROTECTEDTEXTATTRIBUTE4>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute4)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE4>

      \<imp1:PROTECTEDNUMBERATTRIBUTE1>

        \<xsl:value-of select="string(normalize-space(top:protectednumberattribute1))"/>

      \</imp1:PROTECTEDNUMBERATTRIBUTE1>

      \<imp1:PROTECTEDTEXTATTRIBUTE3>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute3)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE3>

      \<imp1:PROTECTEDDATEATTRIBUTE4>

        \<xsl:value-of select="string(normalize-space(top:protecteddateattribute4))"/>

      \</imp1:PROTECTEDDATEATTRIBUTE4>

      \<imp1:PROTECTEDTEXTATTRIBUTE7>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute7)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE7>

      \<imp1:PROTECTEDTEXTATTRIBUTE11>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute11)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE11>

      \<imp1:PROTECTEDTEXTATTRIBUTE6>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute6)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE6>

      \<imp1:PROTECTEDTEXTATTRIBUTE5>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute5)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE5>

      \<imp1:PROTECTEDTEXTATTRIBUTE1>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute1)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE1>

      \<imp1:PROTECTEDTEXTATTRIBUTE8>

        \<xsl:value-of select="normalize-space(top:protectedtextattribute8)"/>

      \</imp1:PROTECTEDTEXTATTRIBUTE8>

    \</imp1:single-record>

  \</xsl:for-each>

\</imp1:Root-Element>

</xsl:template>

</xsl:stylesheet>

Note: This does deploy to E.M. and when tested results in same issue as originally reported. It's also generating error when trying to view Transform Design.

=====================================================================================================================================

Solution #2 - wraps each/all columns in logic below. This is also deploying to E.M. but instance running in error & generating different error when trying to view Transform Design.

TRANSFORM LOGIC:

<xsl:choose>

   \<xsl:when test="string-length(normalize-space(top:protectednumberattribute3))>0">

         \<xsl:value-of select="string(normalize-space(top:protectednumberattribute3))"/>

   \</xsl:when>

   \<xsl:otherwise>

         \<xsl:value-of select="''"/>

   \</xsl:otherwise>

</xsl:choose>

E.M. INSTANCE ERROR:

| Non Recoverable System Fault : |

<bpelFault><faultType>0</faultType><bindingFault xmlns="http://schemas.oracle.com/bpel/extension"><part name="summary"><summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Write' failed due to: Translation Error. Translation Error. Error while translating message to native format. Please make sure that the payload for the outbound interaction conforms to the schema. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. </summary></part><part name="detail"><detail>Invalid text '29WXYZHH000026756LG ELECTRO2018-04-19T00:00:00.000-04:002018-04-26T00:00:00.000-04:002018-05-08T11:29:05.000-04:00UmRmD029UM25.52IN086339432018-03-27T00:00:00.000-04:0000896424415089DCRMCUST' in element: 'single-record'</detail></part><part name="code"><code>null</code></part></bindingFault></bpelFault>

VIEW TRANSFORM DESIGN ERROR:

** In regards to error above, there is one (1) <when> Element. Why does mapper file indicate node already mapped?

Martien van den Akker

Answer

Hi,

Is the output a CSV file?

Could it be that at the particular row the pvndr_nm column contains a comma, while it is not enclosed in quotes?

Taking another look at you first screendump, I gues the problem could be in that third column, that is in your transformation the target element "PROTECTEDTEXTATTRIBUTE9".

Could you show the faulty row in your target file with the row above and below?

Regards,
Martien

Marked as Answer by 2734595 · Sep 27 2020

1 person found this helpful

2734595

Not sure how you knew there was a comma messing things up in PVNDR_NM column but that was the issue. Very impressed and appreciative. Thank you!

I tried to handle this with Replace logic in Transform source code but that didn't work. Translate did though.

<imp1:PROTECTEDTEXTATTRIBUTE10>

<xsl:value-of select="translate(normalize-space(top:protectedtextattribute10),',','')"/>

</imp1:PROTECTEDTEXTATTRIBUTE10>

Martien van den Akker

You're welcome.

But it's in figuring how csv works (although you did not mention that it was a csv).

If the columns aren't enclosed in quotes, then a comma in the text is interpretted as a seperation comma. And that will add a cell in that row.

Glad it's sorted out.

Regards,

Martien

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 12 2022

Added on Aug 17 2020

13 comments

43,979 views

SQL & PL/SQL

Removing Space from string

Comments

Post Details