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

Creating System Stored Procedures

So what are the steps to creating a systemwide stored procedure in SQL Server?

• Create them in the Master Database
• Name must start with "sp_"
• Mark them as System Objects using
• for sql 2000 - master.dbo.sp_MS_upd_sysobj_category
• For 2005 and later - sys.sp_MS_marksystemobject

For a test in 2008:

1. Create a stored procedure in the system databaseone called sp_test
2. Register them as system stored procs using sp_MS_marksystemobject.
3. Then try to use them each from another database without prefixing them with master.dbo.