1

I'm working on a Migration Script wherein I need to extract all client address. The address is only allowed to contain the following characters a-z 0-9 A-Z and special characters ' ( ) + , - . : and the rest should be replaced with white space.

I need to perform a Select Query wherein I need to get all the address that has other characters present except the allowed characters

Select address from client_info where address like '%`~!@#$%^&*_{}[]<>?/%'

the idea is something like that

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Ryan Abarquez
  • 307
  • 2
  • 6
  • 16

2 Answers2

2

You can use REGEX IN MySQL

Select address from client_info WHERE address REGEX '[^-a-zA-Z0-9.,()%+]'

In SQL you can use

Select address from client_info WHERE address LIKE '%[^0-9a-zA-Z ]%'

^ symbolizes NOT - it will fetch the characters other than 0-9 or a-z or A-Z.

MusicLovingIndianGirl
  • 5,909
  • 9
  • 34
  • 65
1

In MS SQL:

SELECT address FROM client_info WHERE address LIKE '%[^a-zA-Z0-9]%'
Nguyễn Hải Triều
  • 1,454
  • 1
  • 8
  • 14