ok guys I think I best show you the full query to see where I am going wrong. OH bare in mind some of the values in the columns have 1.x.x or 8.xx.x these seem to be the ones that cause the problem

Here is the query

use TestData

go

drop table BigTable

drop table RefTable

SELECT *

INTO RefTable

FROM dbo.The_Big_Kahuna

order by dbo.The_Big_Kahuna.Software_Name_Raw

SELECT *

INTO BigTable

FROM dbo.The_Big_Kahuna

order by dbo.The_Big_Kahuna.Software_Name_Raw;

SET NOCOUNT ON

BEGIN TRANSACTION Inner2;

GO

update dbo.BigTable set BigTable.Software_Version_Raw =

case

when CHARINDEX('.',Software_Version_Raw,0) >0 then

substring(Software_Version_Raw,0, CHARINDEX('.',Software_Version_Raw,0) )+ '.x'

when isnumeric(Software_Version_Raw)=1 then

Software_Version_Raw + '.x'

else

Software_Version_Raw + '.x'

end

go

COMMIT TRANSACTION Inner2;

drop table ResultTable

SELECT *

INTO ResultTable

FROM dbo.BigTable

order by dbo.BigTable.Software_Name_Raw;

select * from ResultTable

where software_name_raw like '%office%'

order by dbo.ResultTable.Software_Name_Raw