Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
T-SQL 6
SQL Server 5
#SQL Server 5
#TSQL 4
BRH 3

Archive · View All
October 2010 2
April 2012 1
October 2011 1
September 2011 1
May 2011 1
April 2010 1
March 2010 1

Remove Bad Characters from a string

Oct 19 2011 12:00AM by Naomi   

This is a short blog post for the problem I solved today in MSDN forum. The task was to remove all bad chars from a string allowing only good characters (spaces, letters and numbers). I solved it using a PATINDEX function and while loop, although I am sure there are better approaches available. Anyway, may be this can help someone:

CREATE FUNCTION dbo.RemoveBadChars 
               (@String         VARCHAR(8000), 
                @AllowedPattern VARCHAR(100)) 
RETURNS VARCHAR(8000) 
AS 
  BEGIN 
    DECLARE  @nBadChars INT, 
             @Rest      VARCHAR(100)     
    
     
    SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String) 
     
    WHILE @nBadChars > 0 
      BEGIN 
        SET @Rest = Substring(@String,@nBadChars + 1,Len(@String)) 
         
        SET @String = Substring(@String,1,@nBadChars - 1) 
         
        SET @Rest = Substring(@Rest,Patindex('%[' + @AllowedPattern + ']%',@Rest), 
                              Len(@Rest)) 
         
        SET @String = @String + @Rest 
         
        SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String) 
      END 
     
    RETURN @String 
  END 

GO

to test:

SELECT dbo.RemoveBadChars('1234@@@@@@@@@????????djwkejwk^&*-+=a',' 0-9a-z')

I also found the following blog post later on this topic.

Tags: #SQL Server, SQL Server,


Naomi
31 · 6% · 1776
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • Other related methods are http://beyondrelational.com/blogs/madhivanan/archive/2009/12/02/removing-unwanted-characters-part-2.aspx

    commented on Nov 1 2011 7:56AM
    Madhivanan
    3 · 40% · 12935
  • The difference between our approaches is that I only know the allowed characters and if your case you have a list of bad characters to remove. So, the problems, although related, are slightly different. BTW, my other blog post http://beyondrelational.com/blogs/naomi/archive/2011/09/08/cleanse-all-character-fields-in-a-table.aspx is very similar to your dynamic solution.

    commented on Nov 1 2011 1:12PM
    Naomi
    31 · 6% · 1776
  • Here's another method, without using a LOOP. You'd need to convert the code slightly to make it into a function of course, and it'd be better to have a real tally table rather than the CTE version I have created.

    DECLARE @String VARCHAR(8000), 
    @AllowedPattern VARCHAR(100), 
    @Holder VARCHAR(8000)
    SET @String = '!@#$% 123 ??abc##rd,,,' 
    SET @AllowedPattern = '0-9a-z' 
    SET @Holder = '' 
    
    ;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N), 
    t2 AS (SELECT 1 N FROM t1 x, t1 y), 
    t3 AS (SELECT 1 N FROM t2 x, t2 y), 
    t4 AS (SELECT 1 N FROM t3 x, t3 y), 
    tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
              FROM t4 x, t4 y) 
    SELECT @Holder = @Holder + SUBSTRING(@String,num,1) 
    FROM tally 
    WHERE num <= LEN(@String) 
    AND PATINDEX('['+@AllowedPattern+']',SUBSTRING(@String,num,1)) = 1 
    
    SELECT @Holder
    
    commented on Nov 2 2011 5:46AM
    Cadavre
    207 · 1% · 225
  • Yes, this is nice and simple approach.

    commented on Nov 2 2011 1:13PM
    Naomi
    31 · 6% · 1776

Your Comment


Sign Up or Login to post a comment.

"Remove Bad Characters from a string" rated 5 out of 5 by 3 readers
Remove Bad Characters from a string , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]