0

The goal is to strip all special characters from our customer's addresses.

We learned recently that having any special characters in any part of a customer's record goes against the Metro2 formatting required for credit reporting. I have over 200,000 customer accounts in my database, so going one at a time to clean up these accounts is not an option.

I have access to a live database and a training database. In the training database, the following query works just fine, but it does not work in the live database.

SELECT cus_acct, cus_name, cus_addr1, cus_addr2, cus_city, cus_st, cus_zip, cus_country
FROM sccust
WHERE cus_addr1 IS NOT NULL
AND cus_addr1 <> ' '
AND cus_addr1 LIKE '%*%'
--AND cus_addr1 LIKE '%.%'
--AND cus_addr1 LIKE '%,%'
--AND cus_addr1 LIKE '%''%'
--AND cus_addr1 LIKE '%:%'

UPDATE sccust
SET cus_addr1 = REPLACE(cus_addr1, '%#%', ' ');

Any help is greatly appreciated here!

Executing this query in a training system works perfectly. Simply changing to the live data set produces no results, even though the code has stayed the same.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
NTucker1991
  • 1
  • 1
  • 1
    `REPLACE()` doesn't take wildcards like that, did you mean `REPLACE(cus_addr1, '#', ' ');`? Maybe you could build a repro somewhere so we understand what is and isn't working (is it the update? the select? both?). – Stuck at 1337 Oct 27 '22 at 14:10
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Oct 27 '22 at 14:18
  • 1
    Does either of these answer your question? [Keep only allowed characters in a string](https://stackoverflow.com/questions/74196809/keep-only-allowed-characters-in-a-string) [Comparing and replacing characters in a string in SQL Server](https://stackoverflow.com/questions/71662457/comparing-and-replacing-characters-in-a-string-in-sql-server) – Thom A Oct 27 '22 at 14:21
  • @RhythmWasaLurker That was it. I feel silly for forgetting that. Thanks for the quick response! – NTucker1991 Oct 27 '22 at 16:00

1 Answers1

0

I use to generate this kinda of script by python,I mean instead of writing all this lines of code I prefer scraping data from some web page and build the query via python... anyway it's something like this what u should do

IF EXISTS (SELECT * FROM sys.objects WHERE name='PURGE2HTML_FN' AND 
type='FN')
DROP FUNCTION PURGE2HTML_FN
GO
CREATE FUNCTION PURGE2HTML_FN(@INVAR VARCHAR(MAX), @LEN INT=0) 
RETURNS VARCHAR(MAX) AS
BEGIN
        DECLARE @RETVAL VARCHAR(MAX)

        SET @RETVAL=ISNULL(@INVAR,'')

        SET @RETVAL = REPLACE(@RETVAL, '&nbsp;', '&#160;')
        SET @RETVAL = REPLACE(@RETVAL, '&iexcl;', '&#161;')
        SET @RETVAL = REPLACE(@RETVAL, '&cent;', '&#162;')
        SET @RETVAL = REPLACE(@RETVAL, '&pound;', '&#163;')
        SET @RETVAL = REPLACE(@RETVAL, '&curren;', '&#164;')
        SET @RETVAL = REPLACE(@RETVAL, '&yen;', '&#165;')
        SET @RETVAL = REPLACE(@RETVAL, '&brvbar;', '&#166;')
        SET @RETVAL = REPLACE(@RETVAL, '&sect;', '&#167;')
        SET @RETVAL = REPLACE(@RETVAL, '&uml;', '&#168;')
        SET @RETVAL = REPLACE(@RETVAL, '&copy;', '&#169;')
        SET @RETVAL = REPLACE(@RETVAL, '&ordf;', '&#170;')

        --    ......... *I've cutted some line for the example*

        WHILE CHARINDEX('  ', @RETVAL)>0
                BEGIN
                    SET @RETVAL = REPLACE(@RETVAL, '  ', ' ')
                END

        SET @RETVAL = LTRIM(@RETVAL)
        SET @RETVAL = RTRIM(@RETVAL)



        IF @LEN != 0 
        begin
            SET @RETVAL = LEFT(@RETVAL, @LEN)
        end

        RETURN(@RETVAL)
END
Zakaria Matlaoui
  • 91
  • 2