Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1306
  • Last Modified:

sql query remove special characters from a string

I want to remove special characters from a string in sql query
@mystring="ddss$ss"
to @mystring="ddssss"
@mystring="jon doe"
to @mystring="jondoe"
0
Angela4eva
Asked:
Angela4eva
1 Solution
 
BitsqueezerCommented:
Hi,

for this you can use the T-SQL function "Replace". You would need one Replace call for each character (or set of characters). Here is a solution where the author used a match table to look for the wanted replacement characters and replace it in a target table:

Replace data of one table with data of other table

This is a lot more comfortable as you only need to change the records in the match table if you want to replace different, more or less characters without changing the code.

Cheers,

Christian
0
 
Om PrakashCommented:
Example for your variables & data:

DECLARE @mystring VARCHAR(50)
SET @mystring = 'ddss$ss'
SET @mystring = REPLACE(@mystring, '$', '')
SELECT @mystring

DECLARE @mystring2 VARCHAR(50)
SET @mystring2 = 'jon doe'
SET @mystring2 = REPLACE(@mystring2, ' ', '')
SELECT @mystring2
0
 
SharathData EngineerCommented:
How many special characters do you have?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GRayLCommented:
Upper Case, Numbers ??

Any character not like [a-z] ??

I suggest you loop through each character in the string and if it doesn't pass the test above, append a null, else the character.
0
 
kamindaCommented:
This is a function you can use to do this , Credit goes to the author from here
http://www.source-code.biz/snippets/mssql/1.htm
-- Removes special characters from a string value.
-- All characters except 0-9, a-z and A-Z are removed and
-- the remaining characters are returned.
-- Author: Christian d'Heureuse, www.source-code.biz
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

--------------------------------------------------------------------------------

Example of how to use the function:

  select dbo.RemoveSpecialChars('abc-123+ABC')Result:

  abc123ABC

--------------------------------------------------------------------------------

Open in new window

0
 
Angela4evaAuthor Commented:
great solution
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now