Sampath Sampath - 5 months ago 21
SQL Question

Remove unnecessary Characters by using SQL query

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


This column is
company name
column.I need to keep
string characters only
.But I need to remove
numbers only rows,numbers and characters rows,NULL,Empty and all other +,-
.

enter image description here

Answer

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.