Oracle regexp_replace - removing trailing spaces

Question

I am looking for some help in regards to removing trailing spaces from my queue names. The following is an example of a table that I am using:

QUEUE_NAME
Queue A 
Queue B
Queue C

The problem I have is that there is an extra space at the end of the queue name and when trying the following code:

SELECT
 TRIM(TRAILING ' ' FROM QUEUE_NAME)
FROM
 TABLE_QUEUE;

the space is still there.

I was reading the searches from Google and came across the following code to remove special characters [https://community.oracle.com/blogs/bbrumm/2016/12/11/how-to-replace-special-characters-in-oracle-sql] and this removed all the spaces including the one at the end. The code I wrote:

SELECT
     REGEXP_REPLACE(QUEUE_NAME, '[^0-9A-Za-z]', '')
    FROM
     TABLE_QUEUE;

Only issue I have now is that my result is shown as the following:

QUEUE_NAME
QueueA
QueueB
QueueC

I have never really used regexp_replace hence not sure what I need to change to the code to leave the spaces in between the queue names, so would really appreciate it if somebody could advise on how I could fix this.

Thanks in advance.

---- code edited as should not include [.!?]+

Are you sure it is space and not other whitespace character? — Lukasz Szozda
– Lukasz Szozda, Commented Oct 31, 2018 at 20:40
Hi, Lukasz, to be fair I wasn't sure, its only when i download the table data as CSV that's when I see an extra space at the end. Is there anyway for me to check? — Michael Owen
– Michael Owen, Commented Oct 31, 2018 at 20:41
Notepad++ and show nonvisible characters "View->Show Symbol->Show all characters" — Lukasz Szozda
– Lukasz Szozda, Commented Oct 31, 2018 at 20:42
For the future, it would be best to know for sure WHAT those mysterious characters were. You can find out if you run select DUMP(queue_name) from table_queue. Read a bit about the DUMP function and play with it, you will find it to be very helpful in many occasions. — user5683823
– user5683823, Commented Oct 31, 2018 at 21:16

Barbaros Özhan · Accepted Answer · 2018-10-31 20:39:00Z

1

You may try to use trim only as in the following select statement :

with t(col0) as
(
 select ' Queue A  ' from dual union all
 select '   Queue B ' from dual union all
 select ' Queue C  ' from dual 
)
select trim(col0)
  from t;

trimmedText
-----------
Queue A
Queue B
Queue C

you get no surrounding spaces around.

answered Oct 31, 2018 at 20:39

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Thorsten Kettner · Accepted Answer · 2018-10-31 20:55:23Z

0

You want to remove space from the end of the string:

regexp_replace(queue_name, '[[:space:]]+$', '')

(The '$' in the pattern marks the end.)

If this still doesn't work, then you are dealing with some strange invisible character that is not considered space. Use

regexp_replace(queue_name, '[^0-9A-Za-z]+$', '')

instead, which, as you already know, removes all characters except for letters and digits. The '$' restricts this to the end of the string.

edited Oct 31, 2018 at 20:55

answered Oct 31, 2018 at 20:46

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

8 Comments

Michael Owen Over a year ago

Hi, thanks for your response. Tried that code, still has space at the end

Thorsten Kettner Over a year ago

Okay, strange invisible character then that is not considered space. I've updated my answer.

Michael Owen Over a year ago

Thorsten, thanks for that, that did the trick. The '$' that I added has fixed this. Brilliant.

Michael Owen Over a year ago

For future reference if I wanted to restrict this to the start and end of string, is that possible? As I tried, REGEXP_REPLACE(QUEUE_NAME, '+^[^0-9A-Za-z]+$', '') and that didn't work...

user5683823 Over a year ago

@MichaelOwen - the + sign only means something after a subpattern, not before it. What you do want, instead, is `regexp_replace(queue_name, '^[^0-9A-Za-z]+|[^0-9A-Za-z]+$').

|

Bob Jarvis - Слава Україні · Accepted Answer · 2018-10-31 20:54:00Z

0

Columns of type CHAR (e.g. CHAR(8)) are always blank-padded on the right to the full width of the field. So if you store 'Queue A' in a CHAR(8) field the database helpfully adds a single space to the end of it - and there's no way to remove that extra space from the column. The solution is to change the field so it's defined as either VARCHAR2 (preferred in Oracle) or VARCHAR:

ALTER TABLE TABLE_QUEUE MODIFY QUEUE_NAME VARCHAR2(8);

Then the database will only store the characters you give it, without blank-padding the field.

Best of luck.

answered Oct 31, 2018 at 20:54

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

Oracle regexp_replace - removing trailing spaces

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related