CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
Michael Owen
Michael Owen

Reputation: 375

Oracle regexp_replace - removing trailing spaces

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 [.!?]+

Upvotes: 1

Views: 3645

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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.

Upvotes: 0

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

Reputation: 50017

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.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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.

Upvotes: 1

Related Questions