?
Solved

Update all the fields which has leading zeros with removing leading spaces

Posted on 2007-11-14
10
Medium Priority
?
764 Views
Last Modified: 2012-05-05
I am using oracle data base . I have column which has 2000 records with leading spaces. i want to remove leading whitespaces and store just the number.

Like NDC
       '       1234'   replace record with '1234';


Can anyone tell me how to update records of that type of data.

0
Comment
Question by:cutie_smily
[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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20283451
UPDATE yourtable
  set NDC = trim(leading ' ' from NDC)
WHERE NDC LIKE ' %'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20283453
0
 

Author Comment

by:cutie_smily
ID: 20283495
UPDATE yourtable
  set NDC = trim(leading ' ' from NDC)
WHERE NDC LIKE ' %'


Can you please use fieldname and Tablename in your query. do not use the fieldname i provided 'coz after from is it a table name or columnname??
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:cutie_smily
ID: 20283512
i know the TRIM functions . I am wondering how to apply in my update query.
Thanks and looking forward
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20283560
UPDATE tablename
  set fieldname = trim(leading ' ' from fieldname)
WHERE fieldname LIKE ' %'

?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20283561
UPDATE tablename
  set fieldname= trim(leading ' ' from fieldname
WHERE fieldname LIKE ' %'
0
 

Author Comment

by:cutie_smily
ID: 20283631
Thanks a lot. Let me run above n get back.
0
 
LVL 12

Expert Comment

by:jwahl
ID: 20287480

UPDATE yourtable
SET ndc = LTRIM(ndc)
WHERE ndc LIKE ' %';

Open in new window

0
 
LVL 1

Expert Comment

by:Computer101
ID: 20591481
Forced accept.

Computer101
EE Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 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