Count of special characters in column

Question

I have a table with a list of passwords in all the possible combinations (Special characters, numbers, etc). I want to extract the list of all passwords that have more than 5 special characters.

Need some help on this query

Select Password from Login where Password like `%[a-zA-Z0-9]%` and Len(`%[a-zA-Z0-9]%`) >=5

What do you mean with "special characters"? It's not clear what you are asking and why your current query doesn't work. — Joakim Danielson, May 02 '19 at 05:53
@SebastianBrosch Good point, though passwords should not be encrypted but [salted and hashed.](https://crackstation.net/hashing-security.htm) — Zohar Peled, May 02 '19 at 07:27
@ZoharPeled - Yes salted / hashed is correct (I don't use the right word). Many hashes using certain chars (like MD5 using A to Z and numbers so there can't be a chars like `-/+#!`). — Sebastian Brosch, May 02 '19 at 07:32

Zohar Peled · Answer 1 · 2019-05-02T08:32:27.167

Warning: Your questions suggests you are storing passwords as plain text in your database.
This is a major security risk. Passwords should be stored as salted hash, not encrypted and never as plain text (Thanks to Sebastian Brosch for noticing this).

Having said that, here's an answer to your question:

One way to do it is to break the string into single chars, and then simply query with count:

DECLARE @str nvarchar(30) = 'shS46@($8jr4';
With N10 AS
(
    SELECT N
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))V(N)
), Tally AS
(
    SELECT TOP(LEN(@str)) ROW_NUMBER() OVER(ORDER BY @@SPID) As N
    FROM N10 ten
    CROSS JOIN N10 hundred 
    -- Passwords are usually 10-20 chars max length. 
    -- If you need more you can add another cross join to get 1000.
), Chars AS
(
    SELECT SUBSTRING(@str, N, 1) As C
    FROM Tally
)

SELECT COUNT(*)
FROM Chars
WHERE C NOT LIKE '%[A-Za-z0-9]%'

Of course, if you already have a tally table you don't need to create a tally cte on the fly:

With Chars AS
(
    SELECT SUBSTRING(@str, N, 1) As C
    FROM Tally
    WHERE N <= LEN(@str)
)

SELECT COUNT(*)
FROM Chars
WHERE C NOT LIKE '%[A-Za-z0-9]%'

And a complete version that uses a Login table with a Password column: (and another way to populate an on-the-fly tally cte)

CREATE TABLE Login
(
    Password nvarchar(20)
);

INSERT INTO Login (Password) VALUES
('n9$%^Usj4jjr'),
('Nehj47$%^$'),
('MNAtokay543^A36#$^#%'),
('(*&^#$^dfh$%&'),
('$%^h345nfs54y');



With Tally AS 
(
    SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY @@SPID) As N
    FROM sys.objects

), Chars AS
(
    SELECT Password, SUBSTRING(Password, N, 1) As C
    FROM Login
    CROSS JOIN Tally 
    WHERE N <= LEN(Password)
)

SELECT Password
FROM Chars
GROUP BY Password
HAVING COUNT(CASE WHEN C NOT LIKE '%[A-Za-z0-9]%' THEN 1 END) > 5

Results:

Password
(*&^#$^dfh$%&
MNAtokay543^A36#$^#%

OP wants a list of passwords with 5 or more special characters, maybe you should add that for completeness. — Joakim Danielson, May 02 '19 at 08:02

Ilyes · Answer 2 · 2019-05-02T08:18:27.683

Since your question is How to count of special characters in column? and you don't provide the version you are working on then you can use TRANSLATE(), REPLICATE() and REPLACE() functions as

CREATE TABLE [Login](
  [Password] VARCHAR(45)
);

INSERT INTO [Login] VALUES
('Password'),
('abc@def'),
('a@b@c_d_e/f'),
('Normal');

DECLARE @SC VARCHAR(3) = '@_/';

WITH CTE AS
(
  SELECT *, REPLACE(TRANSLATE([Password], @SC, REPLICATE(' ', LEN(@SC))), ' ', '') Result
  FROM [Login] 
)
SELECT SUM(LEN([Password]) - LEN(Result)) Cnt
FROM CTE;

Returns:

+-----+
| Cnt |
+-----+
|   6 |
+-----+

Here is a db<>fiddle

And also here is another way to do so

CREATE TABLE [Login](
  [Password] VARCHAR(45)
);

INSERT INTO [Login] VALUES
('Password'),
('abc@def'),
('a@b@c_d_e/f'),
('Normal Words');

WITH Chars AS
(
  SELECT SUBSTRING([Password], Number + 1, 1) Chr
  FROM [Login] CROSS JOIN master..spt_values
  WHERE [Type] = 'P'
)
SELECT COUNT(1) Cnt
FROM Chars
WHERE Chr NOT LIKE '%[A-Za-z0-9]%' AND Chr <> '';

Demo

UPDATE:

I want to extract the list of all passwords that have more than 5 special Characters.

CREATE TABLE [Login](
  [Password] VARCHAR(45)
);

INSERT INTO [Login] VALUES
('Password'),
('abc@def'),
('a@b@c_d_e/f$'),
('Normal Words'),
('My_$P@$$word_/');

DECLARE @SC VARCHAR(3) = '$@_/'; --It's just an example
SELECT *
FROM [Login]
WHERE LEN([Password]) - LEN(REPLACE(
                                   TRANSLATE([Password], 
                                             @SC, 
                                             REPLICATE(CHAR(9), LEN(@SC))
                                            ),
                                   CHAR(9), '')) >= 5;

Demo

That would require you to store all "special characters in `@SC`. Not impossible but kind of tedious. It also requires version 2017 or higher since that's when `translate` was introduced. — Zohar Peled, May 02 '19 at 06:25
OP wants a list of passwords with 5 or more special characters, maybe you should add that for completeness. — Joakim Danielson, May 02 '19 at 08:02

score 0 · Answer 3 · answered May 02 '19 at 06:36

Based on your question, i understand that you are calling nonalphanumberic characters as special characters here. If so,you can acheive it in 2 ways.

1) If you know all the list of special character that may appear in your column and if you dont have privilege to create a function.

SELECT * from test 
WHERE (len(COL)- len(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(col,
        '!',''),'@',''),'#',''),'$',''),'%',''),
        '^',''),'&',''),'*',''),' ','')) ) >=5;

2) If you can create a function.

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-Z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

And then select. Thanks to G Mastros for above function

Select * from test where (len(col) - len(dbo.RemoveNonAlphaCharacters(col))) > =5;

DB FIDDLE

score 0 · Answer 4 · answered May 02 '19 at 19:50

Using NGrams8K it's you can simply do this:

--Sample data
CREATE TABLE #Login(ID INT IDENTITY, PW nvarchar(20));
INSERT INTO #Login (PW) VALUES
('n9$%^Usj4jjr'),
('Nehj47$%^$'),
('MNAtokay543^A36#$^#%'),
('(*&^#$^dfh$%&'),
('$%^h345nfs54y');

SELECT      l.ID, SpecialChars = COUNT(*)
FROM        #Login AS l
CROSS APPLY dbo.NGrams8k(l.PW,1) AS ng
WHERE       PATINDEX('[^a-zA-Z0-9]',ng.token) = 1
GROUP BY    l.ID
HAVING      COUNT(*) > 5;

Returns:

ID          SpecialChars
----------- ------------
3           6
4           10

Count of special characters in column

4 Answers4