CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2005
    Posts
    1,828

    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?

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    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

  3. #3
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    4,626

    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,902

    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

  5. #5
    Join Date
    Apr 2005
    Posts
    1,828

    Re: Remove non alphanumeric characters

    Quote Originally Posted by Codeplug View Post
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    1,902

    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

  7. #7
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    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
  •  





Click Here to Expand Forum to Full Width

Featured