Sampath Sampath -5 years ago 155
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download