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
  • 7,993
  • 106
  • 33
  • 46
Ryan Abarquez
  • 307
  • 2
  • 5
  • 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,817
  • 8
  • 33
  • 65
1

In MS SQL:

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