Parsing numbers from strings in SQL

Last updated on  
SELECT Id, PetCount,
ISNULL(SUBSTRING(PetCount,
PATINDEX('%[0-9]%', PetCount),
(CASE WHEN PATINDEX('%[^0-9]%', STUFF(PetCount, 1, (PATINDEX('%[0-9]%', PetCount) - 1), '')) = 0
THEN LEN(PetCount) ELSE (PATINDEX('%[^0-9]%', STUFF(PetCount, 1, (PATINDEX('%[0-9]%', PetCount) - 1), ''))) - 1
END )
), null) AS Cleaned
FROM dbo.MyTable WHERE (PetCount IS NOT NULL OR PetCountLegacy IS NOT NULL) AND LEN(PetCount) > 1