Scripts to Find Line Feeds or Carriage Returns in Purchase Order Parts

04/29/2024 10:03 am EDT

Description of Issue:

If Part codes. Descriptions or other text fields are populated by using Copy/Paste, there could be hidden characters in the field that can cause issues in using parts in orders like POs.

Resolution:

The scripts below can be used to find the records with a problem.

---Backup table --- 

Select * into AP_Purchase_Order_Parts_Backup_123 from AP_Purchase_Order_Parts      


---Remove Line feeds and Carriage Returns from table --- 
---Run the Select portion of the statement to see if this is the issue --- 
---If there are results returned run the update script ---   

UPDATE AP_Purchase_Order_Parts  
set  
Memo = REPLACE(Memo, char(13), ''), 
Vendor_Part_Code = REPLACE(Vendor_Part_Code, char(13), '') , 
Vendor_Part_Description = REPLACE(Vendor_Part_Description, char(13), '') 
--Select * from AP_Purchase_Order_Parts--24 
where Memo like '%'+char(13)+'%'or Vendor_Part_Code like '%'+char(13)+'%' or 
Vendor_Part_Description like '%'+char(13)+'%'   

UPDATE AP_Purchase_Order_Parts  
set  
Memo = REPLACE(Memo, char(10), ''), 
Vendor_Part_Code = REPLACE(Vendor_Part_Code, char(10), '') , 
Vendor_Part_Description = REPLACE(Vendor_Part_Description, char(10), '') 
--Select * from AP_Purchase_Order_Parts 
Where Memo like '%'+char(10)+'%' or Vendor_Part_Code like '%'+char(10)+'%' or Vendor_Part_Description like '%'+char(10)+'%'

Was this article helpful?