Replacing set of character in sql query

This should work for your problem, it removes all non numeric characters from the record. You can create a sql function to be called from your query.

WHILE PATINDEX('%[^0-9]%', @strText) > 0
BEGIN
    SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
END
RETURN @strText

You can create a function as:

CREATE FUNCTION [dbo].[fn_RemoveCharacters]
(
    @Str NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @Str) > 0
        SET @Str = Stuff(@Str, PatIndex(@MatchExpression, @Str), 1, '')

    RETURN @Str

END

Go 

and then call it as:

SELECT [dbo].[fn_RemoveCharacters](ColumnA, '%%!!@@') as ColumnA
from table1

Hope this helps!!!

Please refer how to remove non-numeric/non-alphanumeric characters from string

CREATE FUNCTION [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^a-z0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^a-z0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

select data using the created function

SELECT 
    dbo.fnRemoveNonNumericCharacters(BLOCK_NAME)
FROM 
    YourTable

OR

;WITH T as(
  SELECT STUFF(BLOCK_NAME, PATINDEX('%[^a-z0-9]%', BLOCK_NAME), 1, '') BLOCK_NAME from YourTable
  UNION ALL
  SELECT STUFF(BLOCK_NAME, PATINDEX('%[^a-z0-9]%', BLOCK_NAME), 1, '') BLOCK_NAME from T where PATINDEX('%[^a-z0-9]%', BLOCK_NAME) > 0
)
select 
    * 
from 
    T 
where PATINDEX('%[^a-z0-9]%', BLOCK_NAME) = 0

I prefer the second method using CTE without a user-defined function.