Сообразил такой вариант, прям в запрос, удобно
select c1.value('xs:integer((/v)[1])','int') ci1, c1.value('xs:float((/v)[1])','float') ci1 from (
select cast(' 1234 ' as xml) c1 union all
select cast('1234 Z' as xml) c1 union all
select cast('12,3' as xml) c1 union all
select cast('12.3' as xml) c1 union all
select cast('23e2' as xml) c1
) tt
ci1 ci1
1234 1234
NULL NULL
NULL NULL
NULL 12,3
NULL 2300
select cast('1234' as xml).value('xs:integer((/v)[1])','int') ci1
Это не проверял
CREATE FUNCTION TryCastInt(@inputStr nvarchar(max))
RETURNS int
AS
BEGIN
DECLARE @ret int;
set @ret = -999999
IF (~(cast(patindex('%[^0-9]%', @inputStr) as bit))) >0
select @ret = cast (@inputStr as int)
ELSE
set @ret = -999999
RETURN @ret;
END;
Немного другой вариант, для поразмыслить
DECLARE @VARCHARString VARCHAR(8000)
SET @VARCHARString = '000012W`''ASDASDS3A@!#@!#_)+)_4)'
SELECT @VARCHARString
--Remove non numeric charecters from the varchar variable
WHILE PATINDEX('%[^0-9]%', @VARCHARString) > 0
SET @VARCHARString = STUFF(@VARCHARString, PATINDEX('%[^0-9]%', @VARCHARString), 1, '')
SELECT @VARCHARString + c1 from (
select 1 c1 union all
select 2 c2
) tt