Solved

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

Posted on 2007-11-14
10
760 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 250 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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.

688 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