How to See All the "Special" Characters Permissible in a Varchar or Char Field in SQL Server

How can I see all the special characters permissible in a varchar or char field in SQL Server?

You probably just need to see the ASCII and EXTENDED ASCII character sets. As far as I know any of these are allowed in a char/varchar field.

If you use nchar/nvarchar then it's pretty much any character in any unicode set in the world.

Sample Image

Find all special characters in a column in SQL Server 2008

Negatives are your friend here:

SELECT Col1
FROM TABLE
WHERE Col1 like '%[^a-Z0-9]%'

Which says that you want any rows where Col1 consists of any number of characters, then one character not in the set a-Z0-9, and then any number of characters.

If you have a case sensitive collation, it's important that you use a range that includes both upper and lower case A, a, Z and z, which is what I've given (originally I had it the wrong way around. a comes before A. Z comes after z)

Or, to put it another way, you could have written your original WHERE as:

Col1 LIKE '%[!@#$%]%'

But, as you observed, you'd need to know all of the characters to include in the [].

Identify Special Characters and numbers in SQL

If you want the results just printed out, you can use:

DECLARE @StringVal VARCHAR(400) = 'Test1@abc';  
DECLARE @expres VARCHAR(MAX) = '%[^a-zA-Z_]%';

WHILE @StringVal LIKE @expres
BEGIN
    PRINT SUBSTRING(@stringVal, PATINDEX(@expres, @stringVal), 1);
    SET @stringval = STUFF(@stringVal, 1, PATINDEX(@expres, @stringVal), '');
END;

If you want them output as a string, you can concatenate them:

DECLARE @StringVal VARCHAR(400) = 'Test1@abc';
DECLARE @expres VARCHAR(MAX) = '%[^a-zA-Z_]%';
DECLARE @output VARCHAR(MAX) = '';

WHILE @StringVal LIKE @expres
BEGIN
    SET @output = @output + SUBSTRING(@stringVal, PATINDEX(@expres, @stringVal), 1);
    SET @stringval = STUFF(@stringVal, 1, PATINDEX(@expres, @stringVal), '');
END;

SELECT @output;

Here is a db<>fiddle.

How can I support special characters in SQL Server?

Have you tried "nvarchar". Change the field/fields into nvarchar instead of varchar where you are doubting special characters will come. Or make the table Collation to utf8_general_ci

How to detect if a string contains special characters?

Assuming SQL Server:

e.g. if you class special characters as anything NOT alphanumeric:

DECLARE @MyString VARCHAR(100)
SET @MyString = 'adgkjb$'

IF (@MyString LIKE '%[^a-zA-Z0-9]%')
    PRINT 'Contains "special" characters'
ELSE
    PRINT 'Does not contain "special" characters'

Just add to other characters you don't class as special, inside the square brackets

Checking if a string is valid with special chars

Escaping is one method.

But if you just want to ignore the readable ASCII characters, then the range could be simplified.

[^ -~] : not between space and ~

-- Sample data
declare @T table (col NVARCHAR(30) collate SQL_Latin1_General_CP850_BIN primary key);
insert into @T (col) values
(N'abc╢123'),
(N'xyz123[}'''),
(N'abc௹123');

-- Query
SELECT col, PATINDEX(N'%[^ -~]%' collate  SQL_Latin1_General_CP850_BIN, col) as pos
FROM @T;

Returns:

col         pos
--------    ----
abc╢123     4
abc௹123     4
xyz123[}'   0

But to also locate the caret and some others, it's more complicated.

Since PATINDEX doesn't have ESCAPE as LIKE does.

-- Sample data
declare @T table (
 id int identity(1,1) primary key, 
 col NVARCHAR(30) collate SQL_Latin1_General_CP850_BIN
 );
insert into @T (col) values
 (N'xyz[123]}''') -- good
 ,(N'abc╢123') -- bad
,(N'abc௹123') -- bad
,(N'def#456') -- bad
,(N'def^456') -- bad
;

-- also locate #, ´ , ` and ^
SELECT col, 
CASE 
WHEN PATINDEX(N'%[^ !"$-_a-z{-~]%' collate  SQL_Latin1_General_CP850_BIN, col) > 0
THEN PATINDEX(N'%[^ !"$-_a-z{-~]%' collate  SQL_Latin1_General_CP850_BIN, col) 
ELSE CHARINDEX(N'^' collate  SQL_Latin1_General_CP850_BIN, col)
END AS pos
FROM @T;

Returns:

xyz[123]}'  0
abc╢123     4
abc௹123     4
def#456     4
def^456     4

How to replace all special characters in string

You can create a User-Defined-Function for something like that.

Then use the UDF in the update.

CREATE FUNCTION [dbo].LowerDashString (@str varchar(255))
RETURNS varchar(255)
AS
BEGIN
    DECLARE @result varchar(255);
    DECLARE @chr varchar(1);
    DECLARE @pos int;
    SET @result = '';
    SET @pos = 1;

    -- lowercase the input and remove the single-quotes
    SET @str = REPLACE(LOWER(@str),'''','');

    -- loop through the characters 
    -- while replacing anything that's not a letter to a dash
    WHILE @pos <= LEN(@str)
    BEGIN

      SET @chr = SUBSTRING(@str, @pos, 1)

      IF @chr LIKE '[a-z]' SET @result += @chr;
      ELSE SET @result += '-';

      SET @pos += 1;
    END;

    -- SET @result = TRIM('-' FROM @result); -- SqlServer 2017 and beyond

    -- multiple dashes to one dash
    WHILE @result LIKE '%--%' SET @result = REPLACE(@result,'--','-');

    RETURN @result;
END;
GO

Example snippet using the function:

-- using a table variable for demonstration purposes
declare @SomeInfo table (Id int primary key identity(1,1) not null, InfoCode varchar(100) not null);

-- sample data
insert into @SomeInfo (InfoCode) values
('Cathe Friedrich''s Low Impact'),
('coffeyfit-cardio-box-&-burn'),
('Jillian Michaels: Cardio'),
('Sleek Technique™'),
('The Dancer''s-workout®');

update @SomeInfo
set InfoCode = dbo.LowerDashString(InfoCode)
where (InfoCode LIKE '%[^A-Z-]%' OR InfoCode != LOWER(InfoCode));

select * 
from @SomeInfo;

Result:

Id  InfoCode
--  -----------------------------
1   cathe-friedrichs-low-impact
2   coffeyfit-cardio-box-burn
3   jillian-michaels-cardio
4   sleek-technique-
5   the-dancers-workout-

Find non-ASCII characters in varchar columns using SQL Server

try something like this:

DECLARE @YourTable table (PK int, col1 varchar(20), col2 varchar(20), col3 varchar(20));
INSERT @YourTable VALUES (1, 'ok','ok','ok');
INSERT @YourTable VALUES (2, 'BA'+char(182)+'D','ok','ok');
INSERT @YourTable VALUES (3, 'ok',char(182)+'BAD','ok');
INSERT @YourTable VALUES (4, 'ok','ok','B'+char(182)+'AD');
INSERT @YourTable VALUES (5, char(182)+'BAD','ok',char(182)+'BAD');
INSERT @YourTable VALUES (6, 'BAD'+char(182),'B'+char(182)+'AD','BAD'+char(182)+char(182)+char(182));

--if you have a Numbers table use that, other wise make one using a CTE
WITH AllNumbers AS
(   SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<1000
)
SELECT 
    pk, 'Col1' BadValueColumn, CONVERT(varchar(20),col1) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
    FROM @YourTable           y
        INNER JOIN AllNumbers n ON n.Number <= LEN(y.col1)
    WHERE ASCII(SUBSTRING(y.col1, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col1, n.Number, 1))>127
UNION
SELECT 
    pk, 'Col2' BadValueColumn, CONVERT(varchar(20),col2) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
    FROM @YourTable           y
        INNER JOIN AllNumbers n ON n.Number <= LEN(y.col2)
    WHERE ASCII(SUBSTRING(y.col2, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col2, n.Number, 1))>127
UNION
SELECT 
    pk, 'Col3' BadValueColumn, CONVERT(varchar(20),col3) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
    FROM @YourTable           y
        INNER JOIN AllNumbers n ON n.Number <= LEN(y.col3)
    WHERE ASCII(SUBSTRING(y.col3, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col3, n.Number, 1))>127
order by 1
OPTION (MAXRECURSION 1000);

OUTPUT:

pk          BadValueColumn BadValue
----------- -------------- --------------------
2           Col1           BA¶D
3           Col2           ¶BAD
4           Col3           B¶AD
5           Col1           ¶BAD
5           Col3           ¶BAD
6           Col1           BAD¶
6           Col2           B¶AD
6           Col3           BAD¶¶¶

(8 row(s) affected)