Do you know how to remove below kind of Characters at once on a query ?
Note : .I'm retrieving this data from the Access app and put only the valid data into the SQL.
select DISTINCT ltrim(rtrim(a.Company)) from [Legacy].[dbo].[Attorney] as a
string characters only
numbers only rows,numbers and characters rows,NULL,Empty and all other +,-
Based on your extremely vague "rules" I am going to make a guess.
Maybe something like this will be somewhere close.
select DISTINCT ltrim(rtrim(a.Company)) from [Legacy].[dbo].[Attorney] as a where LEN(ltrim(rtrim(a.Company))) > 1 and IsNumeric(a.Company) = 0
This will exclude entries that are not at least 2 characters and can't be converted to a number.