cutie_smily
asked on
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.
Like NDC
' 1234' replace record with '1234';
Can anyone tell me how to update records of that type of data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
http://www.techonthenet.com/oracle/functions/trim.php
ASKER
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??
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??
ASKER
i know the TRIM functions . I am wondering how to apply in my update query.
Thanks and looking forward
Thanks and looking forward
UPDATE tablename
set fieldname = trim(leading ' ' from fieldname)
WHERE fieldname LIKE ' %'
?
set fieldname = trim(leading ' ' from fieldname)
WHERE fieldname LIKE ' %'
?
UPDATE tablename
set fieldname= trim(leading ' ' from fieldname
WHERE fieldname LIKE ' %'
set fieldname= trim(leading ' ' from fieldname
WHERE fieldname LIKE ' %'
ASKER
Thanks a lot. Let me run above n get back.
UPDATE yourtable
SET ndc = LTRIM(ndc)
WHERE ndc LIKE ' %';
Forced accept.
Computer101
EE Admin
Computer101
EE Admin