CODEWITHSUNDEEP
sqloracle-database
Animesh Sheolikar
Animesh Sheolikar

Reputation: 75

Remove space with in string in Oracle SQL

Hi i need to remove space within string .can any one help me with these.

String1 = "Biotin-dPEG <REFERENCE ID="9869" TYPE="trademark"/>n-azide products are single molecular weight dPEG <REFERENCE ID="9869" TYPE="trademark"/> products that permit biotin labeling with precise spacing" .

I need to remove space before <REFRENCE ID = "9869 ...> in string1.

Upvotes: 1

Views: 540

Answers (3)

MT0
MT0

Reputation: 167822

Oracle Setup:

CREATE TABLE data ( string ) AS
SELECT 'Biotin-dPEG <REFERENCE ID="9869" TYPE="trademark"/>' FROM DUAL UNION ALL
SELECT 'Biotin-dPEG <REFERENCE TYPE="trademark" ID="9869"/>' FROM DUAL;

Query:

SELECT REGEXP_REPLACE(
         UPDATEXML(
           xml,
           '/root/REFERENCE[@ID="9869"][1]/preceding-sibling::text()',
           RTRIM(
             EXTRACT(
               xml,
               '/root/REFERENCE[@ID="9869"][1]/preceding-sibling::text()'
             )
           )
         ).getClobVal(),
         '^<root>|</root>$|^<root/>$'
       ) AS string
FROM   ( SELECT XMLType( '<root>' || string || '</root>' ) AS xml
         FROM   DUAL );

Output:

STRING
--------------------------------------------------
Biotin-dPEG<REFERENCE ID="9869" TYPE="trademark"/>
Biotin-dPEG<REFERENCE TYPE="trademark" ID="9869"/>

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Using regexp_replace:

with t(col) as (
  select 'Biotin-dPEG <REFERENCE ID="9869" TYPE="trademark"' from dual
)
-- test data. Dont mind the above --

select
  regexp_replace(col, '\s+(<REFERENCE)','\1')
from t;

Produces:

Biotin-dPEG<REFERENCE ID="9869" TYPE="trademark"

Upvotes: 2

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

This will remove all space surrounding = 

select  regexp_replace('String1 = "Biotin-dPEG <REFERENCE ID="9869" TYPE="trademark"/>n-azide products are single molecular weight dPEG <REFERENCE ID = "9869" TYPE="trademark"/> products that permit biotin labeling with precise spacing" .
','\s*=\s*','=')
from    dual

String1="Biotin-dPEG <REFERENCE ID="9869" TYPE="trademark"/>n-azide products are single molecular weight dPEG <REFERENCE ID="9869" TYPE="trademark"/> products that permit biotin labeling with precise spacing" .

Here is a simple demo

select  regexp_replace('A =  1,B=   2,C=3,D=  4,E   =  5','\s*=\s*','=')
from    dual

A=1,B=2,C=3,D=4,E=5

https://docs.oracle.com/cd/E18283_01/olap.112/e17122/dml_functions_2068.htm

https://docs.oracle.com/cd/E18283_01/server.112/e17118/ap_posix.htm#SQLRF020

Upvotes: 0

Related Questions