Hi All, I have columns which have a shipping adress with #. For ex.) Home Depot #123. I want to clean up that data and remove all characters starting with #. The query I am using is SELECT SUBSTRING(shipadr1,1,LEN(shipadr1) - charindex('#',Reverse(shipadr1))) FROM Customers . This query displays what I want the shipadr1 to display. My issue is that I am not writing my UPDATE statement correctly. I'm missing a defining JOIN on itself or something. Here is the Update: BEGIN TRAN UPDATE CUSTOMERS SET ShipAdr1 = (SELECT SUBSTRING(shipadr1,1,LEN(shipadr1) - charindex('#',Reverse(shipadr1))) FROM Customers ) And here is the error: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression. The statement has been terminated. Anyone know how i can write this update so that each shipadr1 in each record gets updated? the PK is CustomerID. Thank you in advance.
The clue is in the error message. Assuming you want to apply this transformation to all records in the Customers table, you want to remove the inner select, thus: BEGIN TRAN UPDATE CUSTOMERS SET ShipAdr1 = SUBSTRING(shipadr1, 1, LEN(shipadr1) - charindex('#', Reverse(shipadr1))) Oh, and you'll be wanting to COMMIT your TRAN as well... COMMIT TRAN
UPDATE Customers SET ShipAdr1 = SUBSTRING(shipadr1,1,CHARINDEX('#', shipadr1+'#')-1) As far as I can see, you mean that you want to terminate the string at the first '#' in the string, which is what your code does. I've simplified it. The transaction is unnecessary. Note that I add a '#' to the string for the charindex test so that it doesn't fail if there is no '#' in the string (sods law)