Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
I have string with special characters.so how to
replace special characters in sql like "<[^>]+/\'.{}()#$*@!:;?>."
Posted 10-Sep-12 1:37am
Edited 10-Sep-12 1:41am
_Amy51.8K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can use REPLACE[^] funcation of sql server to solve the problem. Try this:
DECLARE @text nvarchar(128) = '#124 $99^@'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(@text,
        '!',''),'@',''),'#',''),'$',''),'%',''),
        '^',''),'&',''),'*',''),' ','')
 

--Amit
  Permalink  
Comments
__TR__ at 10-Sep-12 6:46am
   
5+
_Amy at 10-Sep-12 6:52am
   
Thanks Tejus. :)
Navneet Sharma at 26-Jul-13 6:44am
   
Good solution
_Amy at 26-Jul-13 6:45am
   
Thank you Navneet. :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

CREATE FUNCTION [FN_REMOVE_SPECIAL_CHARACTER] (
@INPUT_STRING varchar(300))
RETURNS VARCHAR(300)
AS
BEGIN

--declare @testString varchar(100),
DECLARE @NEWSTRING VARCHAR(100)
-- set @teststring = '@san?poojari(darsh)'
SET @NEWSTRING = @INPUT_STRING ;
With SPECIAL_CHARACTER as
(
SELECT '>' as item
UNION ALL
SELECT '<' as item
UNION ALL
SELECT '(' as item
UNION ALL
SELECT ')' as item
UNION ALL
SELECT '!' as item
UNION ALL
SELECT '?' as item
UNION ALL
SELECT '@' as item
UNION ALL
SELECT '*' as item
UNION ALL
SELECT '%' as item
UNION ALL
SELECT '$' as item
)
SELECT @NEWSTRING = Replace(@NEWSTRING, ITEM, '') FROM SPECIAL_CHARACTER
return @NEWSTRING
END
select dbo.[FN_REMOVE_SPECIAL_CHARACTER] ('@s()antosh')
  Permalink  
Comments
hoda memarzade at 17-Sep-13 7:32am
   
That's Great. thank you
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

DECLARE @str VARCHAR(400)
    DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'
      SET @str = '(remove) ~special~ *characters. from string in sql!'
      WHILE PATINDEX( @expres, @str ) > 0
          SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
 
      SELECT @str
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 /\jmot 200
1 Zoltán Zörgő 175
2 Richard Deeming 170
3 Peter Leow 150
4 Sergey Alexandrovich Kryukov 150
0 Sergey Alexandrovich Kryukov 9,328
1 OriginalGriff 6,831
2 Peter Leow 4,477
3 Zoltán Zörgő 3,984
4 Richard MacCutchan 2,832


Advertise | Privacy | Mobile
Web01 | 2.8.150129.1 | Last Updated 17 Dec 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100