0

My current code works and highlights email addresses with illegal (&hidden) characters that go to downstream systems and break them. Question is can make the statment more efficient and say search for any character between CHAR(1) and CHAR(31) rather than listing each line out ?

SELECT * FROM [Table1].[customer]
  where  
EMAIL_ADDR like '%'+CHAR(1) +'%' or
EMAIL_ADDR like '%'+CHAR(2) +'%' or
EMAIL_ADDR like '%'+CHAR(3) +'%' or
EMAIL_ADDR like '%'+CHAR(4) +'%' or
EMAIL_ADDR like '%'+CHAR(5) +'%' or
EMAIL_ADDR like '%'+CHAR(6) +'%' or
EMAIL_ADDR like '%'+CHAR(7) +'%' or
EMAIL_ADDR like '%'+CHAR(8) +'%' or
EMAIL_ADDR like '%'+CHAR(9) +'%' or
EMAIL_ADDR like '%'+CHAR(10)+'%'  or
EMAIL_ADDR like '%'+CHAR(11)+'%'  or
EMAIL_ADDR like '%'+CHAR(12)+'%'  or
EMAIL_ADDR like '%'+CHAR(13)+'%'  or
EMAIL_ADDR like '%'+CHAR(14)+'%'  or
EMAIL_ADDR like '%'+CHAR(15)+'%'  or
EMAIL_ADDR like '%'+CHAR(16)+'%'  or
EMAIL_ADDR like '%'+CHAR(17)+'%'  or
EMAIL_ADDR like '%'+CHAR(18)+'%'  or
EMAIL_ADDR like '%'+CHAR(19)+'%'  or
EMAIL_ADDR like '%'+CHAR(20)+'%'  or
EMAIL_ADDR like '%'+CHAR(21)+'%'  or
EMAIL_ADDR like '%'+CHAR(22)+'%'  or
EMAIL_ADDR like '%'+CHAR(23)+'%'  or
EMAIL_ADDR like '%'+CHAR(24)+'%'  or
EMAIL_ADDR like '%'+CHAR(25)+'%'  or
EMAIL_ADDR like '%'+CHAR(26)+'%'  or
EMAIL_ADDR like '%'+CHAR(27)+'%'  or
EMAIL_ADDR like '%'+CHAR(28)+'%' or
EMAIL_ADDR like '%'+CHAR(29)+'%' or
EMAIL_ADDR like '%'+CHAR(30)+'%' or
EMAIL_ADDR like '%'+CHAR(31)+'%'
Alex Naysmith
  • 1

1 Answers1

0

In SQL Server, you should be able to use a character class:

where EMAIL_ADDR like '%[' + CHAR(1) + CHAR(2) + CHAR(3) + . . . ']%'

However, it is probably simpler to check for characters that are allowed:

where email_addr like '%[-a-zA-Z0-9_.@]%'

or whatever list of characters you are allowing.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786