Solved

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

Posted on 2007-11-14
10
749 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
10 Comments
 
LVL 142

Accepted Solution

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

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 142

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
update joined tables 2 45
configure dependency in POM for new database 3 25
PL SQL Developer 7 32
Create Index on a Materialized View 5 20
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

808 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