-
March 3rd, 2014, 12:37 AM
#1
Remove non alphanumeric characters
I have created this function that will remove all non apha numeric characters, except spaces.
Code:
ALTER FUNCTION dbo.StripNonAlphaNumerics
(
@s VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @p INT = 1, @n VARCHAR(MAX) = '';
DECLARE @TEMP NVARCHAR(MAX)
WHILE @p <= LEN(@s)
BEGIN
SET @TEMP = SUBSTRING(@s, @p, 1)
IF (@TEMP LIKE '[A-Za-z0-9]' OR @TEMP = ' ')
BEGIN
SET @n += @TEMP;
END
SET @p += 1;
END
RETURN(@n);
END
This function is working fine, but because of this function, the execution has become very slow, it takes more than double time to execute a query. Can someone tell me, how i can optimize this whole thing?
-
March 3rd, 2014, 04:07 AM
#2
Re: Remove non alphanumeric characters
I have no time to test whether it would be better or worse...
You could try to look at this variant (in Community Additions section.)
Victor Nijegorodov
-
March 3rd, 2014, 08:04 AM
#3
Re: Remove non alphanumeric characters
If you are using this to restrict the query, then yes, it will cause slowdowns because you're basically telling the DBM that it can't use its keys and instead needs to process every single record in the DB. If this is a critical part of your app, then you may want to consider adding a field holding only the filtered characters and adding an index on that field.
If it's only used to display/list/report, then there may be ways to do this in a more efficient postprocessing at the client side.
-
March 3rd, 2014, 02:02 PM
#4
Re: Remove non alphanumeric characters
What are the non-alpha-num's in the particular colums, and why do you want to strip them?
gg
-
March 4th, 2014, 02:18 AM
#5
Re: Remove non alphanumeric characters
Originally Posted by Codeplug
What are the non-alpha-num's in the particular colums, and why do you want to strip them?
gg
It is the requirement of the client, that there is a field of type text, which contains so many unnecessary stuffs like symbols, tabs and carriage returns. He just wants texts, numbers and spaces to be displayed from that field, removing all other stuff, that he finds unnecessary.
-
March 4th, 2014, 12:21 PM
#6
Re: Remove non alphanumeric characters
Could try and do it in post-processing. So the SQL statement runs at the same speed as before.
gg
-
March 4th, 2014, 02:00 PM
#7
Re: Remove non alphanumeric characters
If the extra chars need to be permanently stripped, then why not do this as a one-time update?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|