CREATE FUNCTION udf_CleanDataFn (
@Value varchar(255),
@Options varchar(255) )
RETURNS varchar(255)
AS
BEGIN
IF ISNULL(@Value, '') = ''
RETURN ''
DECLARE @DelimOptions varchar(257)
SET @Options = ISNULL(@Options, 'trim')
SET @DelimOptions = ',' + @Options + ','
WHILE CHARINDEX(' ', @DelimOptions) > 0
SET @DelimOptions = REPLACE(@DelimOptions, ' ', '')
WHILE CHARINDEX(CHAR(9), @DelimOptions) > 0
SET @DelimOptions = REPLACE(@DelimOptions, CHAR(9), '')
IF CHARINDEX(',ltrim,', @DelimOptions) > 0
SET @Value = LTRIM(@Value)
IF CHARINDEX(',rtrim,', @DelimOptions) > 0
SET @Value = RTRIM(@Value)
IF CHARINDEX(',trim,', @DelimOptions) > 0
SET @Value = RTRIM(LTRIM(@Value))
IF CHARINDEX(',nocomma,', @DelimOptions) > 0
SET @Value = REPLACE(@Value, ',', '')
SET @Value = REPLACE(@Value, CHAR(9), ' ')
WHILE CHARINDEX(' ', @Value) > 0
SET @Value = REPLACE(@Value, ' ', ' ')
IF CHARINDEX(',nospace,', @DelimOptions) > 0
WHILE CHARINDEX(' ', @Value) > 0
SET @Value = REPLACE(@Value, ' ', '')
DECLARE @asc tinyint,
@chr char(1),
@len tinyint,
@ndx smallint,
@NewValue varchar(255),
@use tinyint,
@WkField varchar(255)
SET @len = LEN(@Value)
SET @ndx = -1
SET @NewValue = ''
SET @WkField = '"' + @Value + '"'
WHILE @ndx <= @len
BEGIN
SET @ndx = @ndx + 1
SET @chr = SUBSTRING(@Value, @ndx, 1)
SET @asc = ASCII(@chr)
SET @use = 0
IF @asc BETWEEN 32 AND 126
SET @use = 1
IF CHARINDEX(',nopunc,', @DelimOptions) > 0
IF @asc < 48 OR @asc BETWEEN 58 AND 64 OR @asc BETWEEN 91 AND 96 OR @asc > 122
IF @asc <> 32
SET @use = 0
IF CHARINDEX(',nonum,', @DelimOptions) > 0
IF @chr LIKE '[0-9]'
SET @use = 0
IF CHARINDEX(',noalpha,', @DelimOptions) > 0
IF @chr LIKE '[A-Za-z]'
SET @use = 0
IF CHARINDEX(',alphaonly,', @DelimOptions) > 0
IF @chr NOT LIKE '[A-Za-z]'
SET @use = 0
IF CHARINDEX(',numonly,', @DelimOptions) > 0 OR CHARINDEX(',numericonly,', @DelimOptions) > 0 OR CHARINDEX(',numberonly,', @DelimOptions) > 0
IF @chr NOT LIKE '[0-9]'
SET @use = 0
IF @use = 1
SET @NewValue = @NewValue + SUBSTRING(@Value, @ndx, 1)
END
RETURN @NewValue
END
Thank you for reading my article. Your feedback is welcome. If you liked this article or are interested in more like these, please click the Yes button for: Was this article helpful?
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (11)
Author
Commented:Commented:
Author
Commented:Commented:
Author
Commented:View More