Remove space with in string in Oracle SQL

Question

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.

Oracle is a database. It is not normally used to do string manipulations on individual values. PL/SQL is a scripting language associated with Oracle. But, if you are using that, then the string is defined using :=, not =. Hence, I don't understand the question. — Gordon Linoff
– Gordon Linoff, Commented Feb 1, 2017 at 11:46
"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> . — Animesh Sheolikar
– Animesh Sheolikar, Commented Feb 1, 2017 at 11:57

Gurwinder Singh · Accepted Answer · 2017-02-01 12:34:48Z

2

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"

edited Feb 1, 2017 at 12:34

answered Feb 1, 2017 at 11:51

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Animesh Sheolikar Over a year ago

Thanks @GurV . i modified this little and it worked .thanks for your valuable time and suggestion.

Animesh Sheolikar Over a year ago

select regexp_replace(col, '\s+(<REFERENCE ID)','\1') from table

Gurwinder Singh Over a year ago

@Animesh The with part was anyway to build a test table using CTE. Glad if it helped

MT0 Over a year ago

What happens if the string is Biotin-dPEG <REFERENCE TYPE="trademark" ID="9869" />?

Gurwinder Singh Over a year ago

@MT0 Previously I thought if we're looking for <REFERENCE ID, but now OP says he want only <REFERENCE. So, updated.

David דודו Markovitz · Accepted Answer · 2017-02-01 11:56:16Z

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

MT0 · Accepted Answer · 2017-02-01 12:38:55Z

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"/>

Collectives™ on Stack Overflow

Remove space with in string in Oracle SQL

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related