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?

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.)

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.

What are the non-alpha-num's in the particular colums, and why do you want to strip them?

gg

Quote:

---

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.

Could try and do it in post-processing. So the SQL statement runs at the same speed as before.

gg

If the extra chars need to be permanently stripped, then why not do this as a one-time update?