1

Is there a way that i can eliminate all 'special characters' from a SQL Server query? Sometimes our users will put odd things like '@' or '&' in their name fields and I am trying to eliminate anything that is not a number or letter. Is this possible?

Thanks in advance.

Yanni
  • 25
  • 1
  • 1
  • 5
  • 3
    What exactly do you mean by "eliminate"? Make the query error out, or remove the characters or...? – Joachim Isaksson Jul 23 '14 at 15:53
  • Will you allow numbers in Names??? – M.Ali Jul 23 '14 at 15:55
  • Well, if someone enters a name like 'Joe & Susan' or '@Sam', I would like to have a report made of those kinds of rows, since they need to be fixed. I am sure there is a way to do this but I can't quite figure it out. I want any special characters listed for correction so I don't have to look for each one. (& and @ are just two examples of characters that should not be allowed). Numbers are OK. – Yanni Jul 23 '14 at 15:56
  • can you catch the error before the SQL query is processed, in whatever application you are using? normally you'd filter that out before it even got inserted. – serakfalcon Jul 23 '14 at 15:57
  • maybe you could use CHARINDEX http://msdn.microsoft.com/en-us/library/ms181984.aspx – jyrkim Jul 23 '14 at 15:59
  • 2
    Eliminating "anything that is not a number or letter" will erroneously flag completely valid names like "Cox-Arquette" (hyphen), "Dell'Abate" (apostrophe) and "Van Slyke" (space). Approach this **very** carefully. – alroc Jul 23 '14 at 16:39

4 Answers4

2

I would use the answer here:

How to strip all non-alphabetic characters from string in SQL Server?

If you cannot create a function, you might be able to use the function's code in your query.

Community
  • 1
  • 1
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
1

Best way, if possible, is to do this before you even get to SQL Server. SQL Server is not the best tool for string manipulation. RegEx in the middle tier would handle it well or you could potentially use CLR in SQL using RegEx. Unsure on performance of latter, so would need to be tested.

Earl G Elliott III
  • 425
  • 2
  • 3
0

There is no out-of-the-box solution, however following function should do it:

CREATE FUNCTION dbo.RemoveSpecial (@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

UPDATE:

You could use a chain of REPLACE statements.

SELECT REPLACE([ColumnName]
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        @String,
                      '9', ''),
                    '8', ''),
                  '7', ''),
                '6', ''),
              '5', ''),
            '4', ''),
          '3', ''),
        '2', ''),
      '1', ''),
    '0', '') FROM [TableName]
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • I don't have permission to create any functions. Is there another way to do this or will I have to get the DBA to implement this? Thanks though. – Yanni Jul 23 '14 at 16:00
  • Here's simmilar question has been answered http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – Matas Vaitkevicius Jul 23 '14 at 16:04
0
DECLARE @str VARCHAR(25)
SET @str = '@#@@#%@mohan2655&$E##'
WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0
          SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')
SELECT @str

may be this works !!!!as per your requirement

mohan111
  • 8,633
  • 4
  • 28
  • 55