Removing spaces from a string, SQL

Question

I'm trying to write a SQL query that will remove all spaces so that if a string only has spaces the final string is just ''.

I've tried this code but apparently it isn't working for more than one space:

regexp_replace(:P14_search_text, '( ){1,}', '')

Being :P14_search_text the string I want to modify.

Any help?

Julien May · Accepted Answer · 2012-12-12 18:17:55Z

7

how about:

regexp_replace(:P14_search_text, '[[:space:]]*', '');

answered Dec 12, 2012 at 18:17

Julien May

2,06115 silver badges18 bronze badges

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

1 Comment

Raúl Núñez Cuevas Over a year ago

It didn't work either :/ I'm starting to think the problem is in my code but I just can't find the flaw...thank you anyway (:

Don · Accepted Answer · 2012-12-12 18:20:52Z

5

try this:

     Select Replace(:P14_search_text, ' ', '');

answered Dec 12, 2012 at 18:20

Don

5553 silver badges12 bronze badges

2 Comments

Don Over a year ago

so, what did you get from that?

Raúl Núñez Cuevas Over a year ago

I'm applying this on a search text box, so when I type just one space, it's good, but whenever I type in more than one space, it doesn't work.

Michael Fredrickson · Accepted Answer · 2012-12-12 18:47:01Z

3

Hope this helps you,

SELECT REGEXP_REPLACE(' Any  String ','( ){1,}','') "REGEXP_REPLACE"  FROM DUAL;
SELECT REGEXP_REPLACE('  ','( ){1,}','') "REGEXP_REPLACE"  FROM DUAL;

edited Dec 12, 2012 at 18:47

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

answered Dec 12, 2012 at 18:27

Mariappan Subramanian

10.2k9 gold badges34 silver badges34 bronze badges

Comments

hahn · Accepted Answer · 2012-12-13 17:42:48Z

3

I tried the same method as @Don suggested and it works in oracle 10 xe.

select replace('     lkjds  d   s   adkj      ', ' ', '') from dual

result

lkjdsdsadkj

answered Dec 13, 2012 at 17:42

hahn

3,6681 gold badge22 silver badges33 bronze badges

Comments

nullrevolution · Accepted Answer · 2012-12-12 18:27:48Z

2

the following query works for me in oracle:

select
    :tst_val AS INPUT,
    regexp_replace(:tst_val, '[[:space:]]*', '') AS MODIFIED
from
    dual

if this query does not work for you, would you show us what results you're getting?

answered Dec 12, 2012 at 18:27

nullrevolution

4,1771 gold badge21 silver badges20 bronze badges

6 Comments

Raúl Núñez Cuevas Over a year ago

It's not working, this is what I'm trying to accomplish: where bobooks.owner <> :app_user and (:P14_search_text IS NULL or regexp_replace(:P14_search_text, '[[:space:]]*', '') = '' or ((lower(:P14_CATEGORY) <> 'title' or (lower(:P14_CATEGORY) = 'title' and lower(bobooks.title) like '%' || lower(:P14_search_text) || '%')) and (lower(:P14_CATEGORY) <> 'author' or (lower(:P14_CATEGORY) = 'author' and lower(bobooks.author) like '%' || lower(:P14_search_text) || '%'))))

nullrevolution Over a year ago

are you in a sql server database or an oracle database?

nullrevolution Over a year ago

is the query just not returning anything or is it giving you an error? and what about the query in my answer? what does it return?

Raúl Núñez Cuevas Over a year ago

It's not returning anything, it's working as if it only changes 1 space, after two spaces I receive nothing, no data, on the other hand when I type in just one space, I receive all available data, just as planned, yoour query does the same

nullrevolution Over a year ago

try changing the expression to regexp_replace(:P14_search_text, '[[:space:]]*', '', 1, 0) to explicitly tell it to replace all occurences. it should do this by default, but apparently is not.

|

Collectives™ on Stack Overflow

Removing spaces from a string, SQL

5 Answers 5

1 Comment

2 Comments

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related