Remove special characters in SQL
11,955
Solution 1
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
Solution 2
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)
Comments
-
Raj almost 2 years
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)