REPLACE function does not replace desired character string

Question

I want to replace all occurrences of a particular single character string (eg.:'^'or ',') when creating a view that is based on a single table. But id does not replace the desired single character in all the the data rows. I know it when I query the newly created view. All fields have varchar datatype.

This is a specific a example where the desire string does not get replaced MAINTENANCEÃ‚Â¿ENHANCED I tried the following and none worked.

SELECT REPLACE('MAINTENANCEÃ‚Â¿ENHANCED',',','')
SELECT REPLACE('MAINTENANCEÃ‚Â¿ENHANCED',char(33),'')

SELECT REPLACE(N'MAINTENANCEÃ‚Â¿ENHANCED',',','')
SELECT REPLACE('MAINTENANCEÃ‚Â¿ENHANCED',N',','')
SELECT REPLACE(CAST('MAINTENANCEÃ‚Â¿ENHANCED' as NVARCHAR(50)),N',','')
SELECT REPLACE(CAST('MAINTENANCEÃ‚Â¿ENHANCED' as VARCHAR(50)),N',','')
SELECT REPLACE(TRY_CAST('MAINTENANCEÃ‚Â¿ENHANCED' as VARCHAR(50)),N',','')
SELECT REPLACE(CONVERT(VARCHAR(50),'MAINTENANCEÃ‚Â¿ENHANCED'), N',','')

Also I performed simple test I copied the comma from the string from where I need it to be replaced (see my code below)

if ',' = '‚' print 1 -- DOES NOT return TRUE. 1st comma is the one I typed in the second argument of the REPLACE function, the 2nd comma is the one copied from the string above.

if ',' = ',' print 1 -- RETURNs TRUE. Both of the commas that I typed in the second argument of the REPLACE function.

Apparently the issue is with my comma in the data source which is being treated as equally. Though the functions below shows that both are varchar. ( https://blog.sqlauthority.com/2013/12/15/sql-server-how-to-identify-datatypes-and-properties-of-variable )

**-- comma from the string** 
DECLARE @myVar VARCHAR(100)
SET @myVar = '‚'
SELECT SQL_VARIANT_PROPERTY(@myVar,'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@myVar,'Precision') Precisions,
SQL_VARIANT_PROPERTY(@myVar,'Scale') Scale,
SQL_VARIANT_PROPERTY(@myVar,'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@myVar,'Collation') Collation,
SQL_VARIANT_PROPERTY(@myVar,'MaxLength') MaxLengths


--**regular comma**
SET @myVar = ','
SELECT SQL_VARIANT_PROPERTY(@myVar,'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@myVar,'Precision') Precisions,
SQL_VARIANT_PROPERTY(@myVar,'Scale') Scale,
SQL_VARIANT_PROPERTY(@myVar,'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@myVar,'Collation') Collation,
SQL_VARIANT_PROPERTY(@myVar,'MaxLength') MaxLengths

Partially this can be resolved using this code below

select Stuff('MAINTENANCEÃ‚Â¿ENHANCED', PatIndex('%[^a-z0-9]%', 'MAINTENANCEÃ‚Â¿ENHANCED'), 1, '')

OUTPUT -- the comma is replaced. That is what is expected. MAINTENANCEÃÂ¿ENHANCED

But it does not work in I have more than 1 comma regardless if I copy it from the data source or type it in myself. ('‚MAINTENANCEÃ‚Â¿ENHANCED')

select Stuff('‚MAINTENANCEÃ‚Â¿ENHANCED', PatIndex('%[^a-z0-9]%', '‚MAINTENANCEÃ‚Â¿ENHANCED'), 1, '')

select  REPLACE(Stuff('‚MAINTENANCEÃ‚Â¿ENHANCED', PatIndex('%[^a-z0-9]%', '‚MAINTENANCEÃ‚Â¿ENHANCED'), 1, ''),',','')

OUTPUT -- the comma is back again. The is the Issues. Only one (first) comma is replaced. AINTENANCEÃ‚Â¿ENHANCED

P.S.

Please refer to my answer below where I resolved all the above described issues except that I need to figure out how to preserver from removal special characters like question marks, parenthetic, etc.

Data Engineer · Answer 1 · 2020-10-17T02:44:59.107

PARTIALLY this can be resolved using this code below that I got from here

 use MyDB;
go
drop function if exists [dbo].[RemoveNonAlphaCharacters]
go
 Create Function  [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^ a-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

SELECT MyDB.dbo.RemoveNonAlphaCharacters(', (/!:\£&^?-:;|\)?%$"éè§°àçò*MAIN,2TENANCEÃ‚Â¿ENHANCED 123 asds %[ ..')

I got this from How to strip all non-alphabetic characters from string in SQL Server?

OUTPUT éèàçòMAIN2TENANCEÃÂENHANCED 123 asds

The issues here that it removes all non-alphabetic string characters such as &^?-:;|)? ]% ;:_|!"

I could not fugure out how to pass regular expression to preserver all (except for comma which needs to be replaced) characters in the printable section of the ASCII table (see example above and follow the links below)

https://www.rexegg.com/regex-quickstart.html http://www.asciitable.com/

REPLACE function does not replace desired character string

1 Answers1