Solved

how to delete extra empty spaces

Posted on 2015-01-20
10
99 Views
Last Modified: 2015-02-02
hi ow can i get value in column which got extra empty spaces and delete those extra spaces

value object  
must be
object
0
Comment
Question by:chalie001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40559474
Hi,

Do you mean ?

TRIM('   word   ')

Regards
0
 
LVL 5

Expert Comment

by:GuruJava
ID: 40559475
Hi you can use

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

TRIM('   tech   ')
Result: 'tech'

TRIM(' '  FROM  '   tech   ')
Result: 'tech'

TRIM(LEADING '0' FROM '000123')
Result: '123'

TRIM(TRAILING '1' FROM 'Tech1')
Result: 'Tech'

TRIM(BOTH '1' FROM '123Tech111')
Result: '23Tech'
0
 

Author Comment

by:chalie001
ID: 40559603
i have a word like this
Object > Object > Basic Item Information > Creation

and sql is

select * from temp_unt
where  learning_unit_name = TRIM(TRAILING ' ' FROM learning_unit_name)

i what empty spaces at the end and/or back of the word not in middle
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40559625
If TRIM isn't working for you, please post sample data and expected results.

Post the data in a 'code' block to preserve the spaces.

Click 'Code' in the menu directly above the box where you type your posts and enter the data inside the tags or select/highlight the text and click 'Code'.

This is a code block

Open in new window

0
 

Author Comment

by:chalie001
ID: 40561321
ok i what

'Object > Object > Basic Item Information > Creation '
or
' Object > Object > Basic Item Information > Creation'

to be like this 
'Object > Object > Basic Item Information > Creation'

Open in new window

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40561676
Here is another option:
select LTRIM(RTRIM(' Object > Object > Basic Item Information > Creation')) col1 from dual;
0
 

Author Comment

by:chalie001
ID: 40561810
if i what to remove those extra empty spaces from all values which got extra empty spaces how can i do that i do this

 
somethint like this

update cal_obj

set obj_name = trim(obj_name)

 
e.g

 
obj_name

'Object > Object > Basic Item Information > Creation '

'Object > Object > Basic Item Information > test '

' Object > Object > Basic Item Information > new'



i what values to be like this after update

obj_name

'Object > Object > Basic Item Information > Creation'

'Object > Object > Basic Item Information > test'

'Object > Object > Basic Item Information > new'
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40561835
>>somethint like this


The trim you posted will do exactly that.  What else is needed?

update some_table set some_column=trim(some_column);
0
 

Author Comment

by:chalie001
ID: 40564162
i have run the following query
update cal_book
         
set description = rtrim(description,' ')
          where obj_name = 'LLCOSD03.form'
          and learning_unit_number = '2';
          
          but after i comit am still geting an extry emty space in front of the value
          select * from cal_book
          where obj_name = 'LLCOSD03.form'
           and learning_unit_number = '2';

Open in new window

e.g

' test value'

to be this after update
'test value'
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40564200
RTRIM trims 'R'ight.

Just use regular TRIM like I posted.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question