Create an SQL query that checks the column name for a select string of words

The format of my data at the moment (av_version is an nvarchar)

company    av_version
company1   8.5.0.440
company1   8.5.0.332
company1   8.5.0.232
company1   8.5.0.111
company2   10.0.0.1509
company2   10.0.0.2323
company2   10.0.0.1232
company2   10.0.0.2253
company3   8.5.0.232
company3   10.0.0.2253

What I need is a query that will check all the entries of the AV version for that company, and if 8.5.0 is reported even开发者_运维知识库 once then that will display as a distinct company. In the example data company1 and company3 would be the returned list. The difficulty here is the end of the av version could be anything. Any help appreciated

---

You can use LIKE:

av_version LIKE '8.5.0%'

---

You can make a select statement that includes a select statement. For example lets assume that the table name is "avcom".

select company, hc_version, max(av_version) as av_version
from 
(
    select company, av_version, '8.5.0' as hc_version
    from avcom
    where av_version like '8.5.0%'
)
group by company, hc_version

The query was not tested! Also, I assumed that you might need something like the latest/biggest version number (max(av_version)) for the result in addition to the '8.5.0' result which is fixed.

---

SELECT DISTINCT company
FROM atable
WHERE av_version = '8.5.0'
   OR av_version LIKE '8.5.0.%'