Remove special characters in SQL

Question

      I have to remove the special characters from the selected string.

for example : I have string 'a&b' or 'a & b'. How can i remove special characters and concatenate these string into 'ab'.

Please can any one tell me.

SELECT @id= (UPPER(SUBSTRING(@a,1,2)))+(UPPER(SUBSTRING((SELECT table.column2 FROM tablename WHERE tablename.column1 = @b),1,2))) + RIGHT('000000000' + CAST(@count as varchar(10)), 6)

possible duplicate of [How to strip all non-alphabetic characters from string in SQL Server?](http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) — Alex K., Aug 09 '11 at 11:01
how can i do that in stored procedure without using function — Raj, Aug 09 '11 at 11:06

score 1 · Answer 1 · edited May 23 '17 at 12:26

The function is by far the best option, if you must do it inline you can replace within a recursive CTE and use that as the base table;

select 1 as id,
       'qw2££!"£$%^&**(' as F into #TESTTABLE
insert #TESTTABLE 
values (2, 'xxx'),
       (3, ''), 
       (4,'$'),
       (5,'qq""ee$$')

;with cte(id, stripped) as (
    select id, cast(F as varchar(1024)) from #TESTTABLE
    union all 
    select id, cast(stuff(stripped, patindex('%[^a-z]%', stripped), 1, '') as varchar(1024))
    from cte
    where patindex('%[^a-z]%', stripped) > 0
)
select * from cte
    where patindex('%[^a-z]%', stripped) = 0
order by id

Result:

>>id  stripped
>>1   qw
>>2   xxx
>>3 
>>4 
>>5   qqee

score 1 · Answer 2 · answered Aug 09 '11 at 11:34

Declare @temp varchar(30)
Set @temp = 'A & B'
While PatIndex('%[^a-z]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')

SELECT @id= (UPPER(SUBSTRING(@Temp,1,2)))+(UPPER(SUBSTRING((SELECT table.column2 FROM tablename WHERE tablename.column1 = @b),1,2))) + RIGHT('000000000' + CAST(@count as varchar(10)), 6)

score 0 · Answer 3 · answered Oct 25 '13 at 12:48

DECLARE @str VARCHAR(400)
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'
  SET @str = 'a&b'
  WHILE PATINDEX( @expres, @str ) > 0
      SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')

  SELECT @str

For More Details Click Here

Remove special characters in SQL

3 Answers3