Solved

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

Posted on 2007-11-14
10
747 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
Comment Utility
UPDATE yourtable
  set NDC = trim(leading ' ' from NDC)
WHERE NDC LIKE ' %'
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0
 

Author Comment

by:cutie_smily
Comment Utility
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
 

Author Comment

by:cutie_smily
Comment Utility
i know the TRIM functions . I am wondering how to apply in my update query.
Thanks and looking forward
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 25

Expert Comment

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

?
0
 
LVL 142

Expert Comment

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

Author Comment

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

Expert Comment

by:jwahl
Comment Utility

UPDATE yourtable

SET ndc = LTRIM(ndc)

WHERE ndc LIKE ' %';

Open in new window

0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 40
Oracle regular expression 6 29
Convert Oracle data into XML document 2 36
Insert with SET how to handle join 6 25
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now