Check names in a database for invalid characters from a list of permitted characters

Question

CREATE PROCEDURE [dbo].[namecheck]
    (@surname varchar(40))
AS
if @surname like '%[^0-9a-zA-Z .,?()\[\]:;''=/@$%*&!"-]%' ESCAPE '\' COLLATE Latin1_General_100_CS_AS
    RETURN 1

RETURN 2
GO

DECLARE @returncode int

EXECUTE @returncode = namecheck 'Bum'

SELECT @returncode AS returncode

EXECUTE @returncode = namecheck 'Bumë' 

SELECT @returncode AS returncode

GO

the return code is the same regardless of the validity of the string eg 'Bum' should be returncode 2 and 'Bumë' should be return code 1

can you please remove the commented code ? It makes the code little more difficult to go through. — Venkataraman R, Feb 23 '22 at 03:25
if your requirement is to remove NON-ASCII characters, you can refer to post: https://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-varchar-columns-using-sql-server — Venkataraman R, Feb 23 '22 at 03:51

Tim Biegeleisen · Answer 1 · 2022-02-23T05:16:17.530

0

You should check for the presence of any non whitelisted characters:

if @surname like '%[^0-9a-zA-Z .,?()\[\]:;''=/@$%*&!"-]%' escape '\' collate Latin1_General_100_CS_AS
    begin
    select @message  = 'The surname name contains invalid characters'
    return 13
    end
else
    begin
    select @message  = 'The surname name contains valid characters'
    return 12
    end

edited Feb 23 '22 at 05:16

answered Feb 23 '22 at 03:25

Tim Biegeleisen

502,043
27
286
360

1

Why there is `;` after begin ? Why do we need it ? can you please explain ? – Venkataraman R Feb 23 '22 at 03:27
That still doesn't produce the correct message. The value is "Bum" which only contains valid characters. The : after begin is a future SQLServer requirement. It will currently work without it unless you have "rollback transaction" instead of "return" – Warlock Feb 23 '22 at 05:14
Fix the semicolon problem and it should work. – Tim Biegeleisen Feb 23 '22 at 05:16
Removing the semi-colon makes no difference. – Warlock Feb 23 '22 at 05:18

SQLpro · Answer 2 · 2022-02-24T11:43:55.687

0

Something like :

CREATE OR ALTER FUNCTION dbo.F_VALID_STRING (@STRING NVARCHAR(max))
RETURNS BIT
AS
BEGIN
IF @STRING NOT LIKE REPLICATE('[a-zA-Z0-9]', LEN (@STRING)) COLLATE Latin1_General_BIN
   RETURN 0;
DECLARE @STR NVARCHAR(max) = TRANSLATE(@STRING, ' \.,?()[]:;''-=/@$%*!"', '&&&&&&&&&&&&&&&&&&&&&');
SET @STR = REPLACE(@STR, '&', '');
IF LEN(@STR) <> LEN(@STRING)
   RETURN 0;
RETURN 1
END
GO

May help you...

edited Feb 24 '22 at 11:43

answered Feb 23 '22 at 13:03

SQLpro

3,994
1
6
14

The function still returns the same code for both valid and invalid characters in a string – Warlock Feb 24 '22 at 01:54
Sorry I rectify for accents – SQLpro Feb 24 '22 at 11:44
The permitted characters are ([0-9a-zA-Z \.,\?\[\]:;'\-=\/@$%\*&!"])* with '|' as the ESCAPE character. It now traps more than it should. – Warlock Feb 25 '22 at 23:20

Check names in a database for invalid characters from a list of permitted characters

2 Answers2