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 0:37am
Edited 10-Sep-12 0:41am
_Amy48.5K
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

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 483
1 OriginalGriff 333
2 Maciej Los 305
3 CHill60 220
4 manoj kumar choubey 184
0 OriginalGriff 6,367
1 Sergey Alexandrovich Kryukov 5,296
2 Maciej Los 3,384
3 Peter Leow 3,259
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 10 Sep 2012
Copyright © CodeProject, 1999-2014
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