Remove Spaces and Non Alpahnumeric Characters

SELECTRTRIM(CUSTNMBR)AS CUSTNMBR
,REPLACE(REPLACE(CUSTNMBR ,SUBSTRING(CUSTNMBR ,PATINDEX('%[^a-zA-Z0-9 ]%' ,CUSTNMBR) ,1) ,'') ,CHAR(32) ,'')AS NU_CUSTNMBR

,CUSTNAME


FROM RECORDSTABLE


WHERECUSTNMBR LIKE '%[^a-zA-Z0-9 ]%'

OR CHARINDEX(CHAR(32) ,RTRIM(CUSTNMBR)) > 0;

Strip unwanted characters out of sql string or variant data

-- =============================================

-- Author:           blowers

-- Create date: 2012-05-11

-- Description:      function to strip out special (escape and other) characters from an input string

-- MODIFY @KEEP TO INCLUDE THE CHARACTERS YOU WISH TO KEEP

--     - in the example a-z, 1-9 and a period will be retained and all others removed

--     - go be the uber-dba and create a list of formats you might want to use (numeric only, alpha only, money formatting only, etc)

-- =============================================

CREATE Function [dbo].[strip_special](@Temp VarChar(1000))

Returns VarChar(1000)

AS

       Begin

              DECLARE @KEEP VARCHAR(50)

              SET @KEEP = '%[^a-z0-9.]%'

              While PatIndex(@KEEP, @Temp) > 0

                     Set @Temp = Stuff(@Temp, PatIndex(@KEEP, @Temp), 1, '')

              Return @TEmp

       End

GO