July 8, 2004 at 11:44 am
I have an application which is required to import data from various file types which are manually created. One serious problem exists when special characters are included such as exclamation,question mark, comma, semicolon, etc. These are identified a suspected errors and changed but, until that happens, I need to be able to exclude them from certain stored procedures which must use clean values. How would a stored procedure be written to include/exclude certain ASCII values? This is my concept for designing the procedure but there may be others. Any suggestions would be welcomed.
July 8, 2004 at 9:53 pm
July 9, 2004 at 7:30 am
A couple of days ago there was a post regarding how to implemenent the Oracle function TRANSLATE in SQL Server for which I wrote a UDF.
Try the following UDF. Run the create function code, the try the following example. The function works by scanning the first parameter (source string) for all occurrences of each character in the second string (searchMap). If the searchMap character is found, it is replaced by the character in the parameter (replacementMap) which appears in the same position. Think of the searchMap and replacementMap as arrays of single characters.
If you specifiy an empty string for the third parameter, all of the searchMap characters will be deleted instead of replaced.
----------------------------------------------------------------------
declare @s-2 varchar(100)
set @s-2 = 'This string""|! contains some@@ invalid cahracters,' + char(1)+char(2) + ' including some non-printables.'
print @s-2
print dbo.fnTranslate(@s, '"|!@'+char(1)+char(2), '')
GO
CREATE FUNCTION dbo.fnTranslate
(
@sourceString varchar(8000),
@searchMap varchar(8000),
@replacementMap varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @cPos int, @maxCPos int
SET @maxCPos = Len(@searchMap)
SET @cPos = 1
WHILE @cpos <= @maxCPos
BEGIN
SET @sourceString = Replace(@sourceString, SubString(@searchMap, @cpos, 1), SubString(@replacementMap, @cpos, 1))
SET @cPos = @cPos + 1
END
RETURN @sourceString
END
July 9, 2004 at 10:54 am
You could also use the following and be able to specify only the characters you want to show up. Some of my data exports require alpha numeric only - no special characters. I would use '[^A-Z 0-9]' in that case.
-- This UDF will filter everything out execpt those characters you specify.
CREATE FUNCTION dbo.FilterStr
(@UDF_str VARCHAR(1000), @valid_values VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN
WHILE PATINDEX('%' + @valid_values + '%',@UDF_str) > 0
SET @UDF_str=LEFT(@UDF_str ,PATINDEX('%' + @valid_values + '%',@UDF_str)-1)+
SUBSTRING(@UDF_str ,PATINDEX('%' + @valid_values + '%',@UDF_str)+1,1000)
RETURN @UDF_str
END
GO
SELECT title, dbo.FilterStr(title,'[^A-Z 0-9.]') FROM pubs.dbo.titles
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply