Divyesh Jesadiya Divyesh Jesadiya - 1 year ago 75
SQL Question

how to update database from query

in my database i have many column like this.

Vendor VZWBuildingNumber VendorSiteID
ADO.NET 0 ACD-MI-12503
ADO.NET 0 ACD-MI-12518
ADO.NET 0 ACD-MI-12517

and i want it like this

Vendor VZWBuildingNumber VendorSiteID
ADO.NET 012503 ACD-MI-12503
ADO.NET 012518 ACD-MI-12518
ADO.NET 012517 ACD-MI-12517

i want last 5 letter of VendorSiteID and it apper in VZWBuildingNumber where vendor = ADO.NET and VZWBuildingNumber = 0. I tried but not any success Please help me with Query.

Answer Source

Mysql's SUBSTRING function is what you are looking for.

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax

Thus you can write your query as

UPDATE VZWBuildingNumber 
SET VZWBuildingNumber = CONCAT('0',SUBSTRING(VendorSiteID, 8, 7))
WHERE Vendor = 'ADO.NET'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download