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.%'