[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

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

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
cutie_smily
Asked:
cutie_smily
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UPDATE yourtable
  set NDC = trim(leading ' ' from NDC)
WHERE NDC LIKE ' %'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
cutie_smilyAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
cutie_smilyAuthor Commented:
i know the TRIM functions . I am wondering how to apply in my update query.
Thanks and looking forward
0
 
imitchieCommented:
UPDATE tablename
  set fieldname = trim(leading ' ' from fieldname)
WHERE fieldname LIKE ' %'

?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UPDATE tablename
  set fieldname= trim(leading ' ' from fieldname
WHERE fieldname LIKE ' %'
0
 
cutie_smilyAuthor Commented:
Thanks a lot. Let me run above n get back.
0
 
jwahlCommented:

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

Open in new window

0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now