question

Noonies avatar image
Noonies asked

UPDATE using SUBSTRING to remove characters

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.
t-sqlquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
good catch on the commit there!
0 Likes 0 ·
Tim avatar image
Tim answered
Remove the from clause out of your SET statement. You are trying to update a single column with your entire table.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Noonies avatar image Noonies commented ·
Thank you both!
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
great tip on preventing the CHARINDEX causing the SUBSTRING to fail.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.