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.