Helo, the same, but in 1-query version

DECLARE @I VARCHAR(100)
SELECT @I ='abhi *s d ^.l++++a+b+c-'

;with cte01 as 
(
   select I=cast(@I as varchar(max)), Lp=1
   union all
   SELECT cast(STUFF(C.I,PATINDEX('%[^A-Za-z0-9 ]%' ,C.I ),1,'') as varchar(max)), Lp=Lp+1
   from cte01 C
   where PATINDEX('%[^A-Za-z0-9 ]%' , C.I ) <> 0 
)
select I
from
(
   select I, rn=ROW_NUMBER() over (order by LP desc)
   from cte01 
)A
where A.rn=1
option (maxrecursion 0)   

Note that the pattern [A-Za-z] is functionally identical to [a-z] or [A-Z] unless a case-sensitive collation is being used--rare in most databases in my experience.

@Zibi:

Good work. If performing the operation against a whole table at once, a CTE could be useful, but otherwise I'd stick with the WHILE loop.

Anyway, I hope you don't mind a variation I made of your query that avoids the row_number() part:

DECLARE @I VARCHAR(100);
SET @I = 'abhi *s d ^.l++++a+b+c-';

WITH DestructoLoop AS (
   SELECT
      I = Convert(varchar(max), @I),
      Flag = CASE WHEN @I LIKE '%[^A-Za-z0-9 ]%' THEN 0 ELSE 1 END
   UNION ALL
   SELECT
      Convert(varchar(max), Stuff(D.I, PatIndex('%[^A-Za-z0-9 ]%', D.I), 1, '')),
      CASE WHEN D.I LIKE '%[^A-Za-z0-9 ]%[^A-Za-z0-9 ]%' THEN 0 ELSE 1 END
   FROM DestructoLoop D
   WHERE PatIndex('%[^A-Za-z0-9 ]%', D.I ) <> 0
)
SELECT I
FROM DestructoLoop
WHERE Flag = 1;

Using a tally table

DECLARE @I VARCHAR(100)
DECLARE @Result VARCHAR(100)
SELECT @Result = ''
SELECT @I ='abhi *s d ^.l++++a+b+c-'

SELECT @Result = CONCAT(@Result,SUBSTRING(@I,N,1) )
FROM dbo.tsqlc_Tally
WHERE N <= DATALENGTH(@I) 
AND SUBSTRING(@I,N,1)  IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N'
,'O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9' ,' ')

SELECT @Result

@Eric Bradford,

Please provide the source or reference for the Concat Function and tsqlc_tally Table.

Also, why not use LIKE '[A-Z0-9]' instead of IN () with 36 items?

From BeyondRelational puzzles

SELECT TOP 1000001 IDENTITY(INT,0,1) AS N
INTO dbo.tsqlc_Tally
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2

Concat function is introduced in SQL2012.

Try this one:

DECLARE @I VARCHAR(100)
DECLARE @Result VARCHAR(100)
SELECT @Result = ''
SELECT @I ='abhi *s d ^.l++++a+b+c-'

SELECT @Result = @Result +SUBSTRING(@I,N,1) 
FROM dbo.tsqlc_Tally
WHERE N <= DATALENGTH(@I) 
AND SUBSTRING(@I,N,1)  IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N'
,'O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9' ,' ')

SELECT @Result

This code is more efficient then using patindex.

Helo, funny game. And I want to add some more. Totaly another way.

;with cte0 as (
  select I=replace(replace(replace(@I, '<', ''), '>', ''), '&', '')
)
select I = cast(
                 cast(replicate('', len(i)) as xml)
                    .query('for $a in /a
                              let $i:=count($a/../a[.<<$a]) 
                              let $s:=substring(sql:column("I"), $i, 1)
                            where contains("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890 ", $s)
                              return {$s}
                            ')
                    .query('/a/text()')
                  as varchar(max)
                 )
from cte0

this sql code is not correct displayed on this page, on page source is perfect. I'm meaning section with return

@Eric Thank you. A SQL 2012 instance is coming to my company in the near future, but not yet... so I am lamentably lacking in some of its features.

There is a three part of mine http://beyondrelational.com/modules/2/blogs/70/posts/14280/removing-unwanted-characters-part-3.aspx

There are some interesting solutions above, and while some are smart and each is worthwhile if only as a teaching example, some are examples of what not to do. I learned something: There is a "contains( )" function; I did not know that. But, ....

Loops: bad! Long quoted strings (lists of all valid characters): bad! Regular expressions: good!

The original solution was smart and elegant and is a good teaching example of how to use CTE's recursion feature. However, I would suggest using the Regexp_Replace( ) function, which has no loops and is very fast. The SQL Server internal engine, of course, will process it in a loop, but a highly optimized one, completely invisible to the user.

Also, I highly recommend adding explanatory comments or at least simple header comment lines to SQL code when using regular expression functions or any where the purpose code that is not immediately obvious to the reader.

Helo, @Henry: this forum is for sharing knowledge. I specially posted my second odd solution with xml to show people new features in SQL (xquery). Maybe in this situation it is not useful, but for teaching - I think - pretty good. :)

Have you tried nesting REPLACE() startements to get rid of unwanted charractrs? It is very fast. Then we can ask MS for the TRANSLATE() function soem day.

Zibi, I agree with you. I did acknowledge the teaching/learning aspect of the solutions.

Mr Celko (famous name, what?), Replace is very fast, but limited to replacing one string or character. Regexp_Replace( ) can use regular expressions to replace a whole set of characters or strings at once.

I also direct peoples' attention to the RegexpSubstr( ) function, which, although not applicable to the solution above, can be used to parse out embedded strings. For example, I have used it to parse out circuit numbers from a description column, where the circuit numbers are in several different formats, some beginning with a single, specific character followed by numbers, some beginning with one of 3 or 4 specific 3 letter prefixes followed by numbers that may have a character intermixed. It would take a lot of if then else code, case-whens, loops, and calls to substr( ) and maybe even patindex( ) to pull those out. I was able to do it without any loops or case-when logic, with just one call to RegexpSubtr( ), although I have to admit the regular expression string was up to 20 maybe 25 chars long and took some time to fine tune it to catch all formats. I can't show you the code.

Just now i learned, How to use "master..spt_values" insted of using while loop.

Sum up digits of a number

This is the script for removing special character s with out while loop.

DECLARE @I VARCHAR(100);
SELECT @I ='abhi *s d ^.l'
DECLARE @STR VARCHAR(100) = ''

select  @STR=@STR+ CASE WHEN PATINDEX('%[^A-Za-z0-9 ]%' , substring(ltrim(@i),number,1) )=  0 THEN substring(ltrim(@i),number,1) ELSE '' END
from    master..spt_values 
where   type='p' and number between 1 and len(@i)
SELECT @STR

Cool!

abhIShek, The solution works but table spt_values is an undocumented table. Shouldn't we be using a table purposely built for counting (i.e. tally table).

table spt_values table may be undocumented, but it's used in a lot of SQL Server system stored procedures, and it's not going away. Besides, if a person wants to create their own "numbers" table (containing sequential integers) -- perhaps because they need a larger range of numbers, it is easy to do.

There are a lot of uses of such a numbers table; the above was a good example. Do a google search for "numbers table sql".

Another useful table is a Dates table. One of the articles listed by the above google search uses a dateadd( ) function that uses the numbers table, but it could also have used a Dates table, which if you populated that once, you would never again have to use a query that called a Dateadd( ) function on every row of a range of numbers. In other words, the DateAdd( ) function could be used (along with the numbers table) to populate a Dates table, which from then on would be available when and where needed. Lots of articles available on how to use a Dates table. The example I mentioned uses a Dates table to join with a roomsrented table to list all rooms available in a range of dates at a hotel.