SQLSRV Update Query to remove spaces

Using a combination of MS-SQL, PHP, and JSON, I have a column in my page called VendorAddress that outputs to the browser like this:

 1111 THAT WAY DRIVE �TORRANCE 

That strange black diamond with the questionmark.

I have narrowed it down to the fact that some users are copying and pasting from Excel, and there is an extra space between the street and city. I can prevent this from happening in the future. It's the ones that already been saved to the table that is causing a problem for me.

I have found there are over 300 records in the MS-SQL database that have this extra space.

What I need to do is write a query that would update the column by removing this extra space.

 UPDATE [main].[dbo].[suppliers] SET [VendorAddress] = '1111 THAT WAY DRIVE TORRANCE' WHERE [PartnerCode] = '00011144'; 

This is the query I was using, going record for record for each PartnerCode that has the black diamond (336 total). I refuse to go record for record on this. I need to write a query that will do it for me.

I am not sure how to go about doing this. Everything I find online tells me to use TRIM, but it's only for trailing spaces. These are not trailing spaces. They are within the string.

Please help me write a query that would look at the whole table and remove the unnecessary spaces from within the string.

Thank you in advance.

Replay

Category: php Time: 2016-07-28 Views: 0
Tags: php sql server

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.215 (s). 12 q(s)