SaintFrag,
First I'd like to say that this isn't a script I created, but I didn't record from whom I got it. Thank you whomever you are:
CREATE FUNCTION dbo.CleanString
/*******************************************************************************
Purpose:
Given a string and a pattern of characters to remove, remove the patterned
characters from the string.
Usage:
--===== Basic Syntax Example
SELECT CleanedString = dbo.CleanString(@pSomeString,@pPattern)
;
--===== Remove all but Alpha characters
SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^A-Za-z]%');
FROM dbo.SomeTable st
;
--===== Remove all but Numeric digits
SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^0-9]%');
FROM dbo.SomeTable st
;
Programmer Notes:
1. @pPattern is case sensitive.
2. The pattern set of characters must be for just one character.
Revision History:
Rev 00 - Circa 2007 - Author Unknown
- Initial find on the web
Rev 01 - 29 Mar 2007 - Jeff Moden
- Optimize to remove one instance of PATINDEX from the loop.
- Add code to use the pattern as a parameter.
Rev 02 - 26 May 2013 - Jeff Moden
- Add case sensitivity
*******************************************************************************/
(@pString VARCHAR(8000),@pPattern VARCHAR(100))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @Pos SMALLINT;
SELECT @Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);
WHILE @Pos > 0
SELECT @pString = STUFF(@pString,@Pos,1,''),
@Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);
RETURN @pString;
END
;
GO
Kind regards,
Leslie
Share
Report
205
All responses (
Answers (
