TRY_CONVERT, TRY_CAST в MSSQL 2005

Сообразил такой вариант, прям в запрос, удобно

select c1.value('xs:integer((/v)[1])','int') ci1, c1.value('xs:float((/v)[1])','float') ci1 from (
	select cast('<v> 1234 </v>' as xml) c1 union all
	select cast('<v>1234 Z</v>' as xml) c1 union all
	select cast('<v>12,3</v>' as xml) c1 union all
	select cast('<v>12.3</v>' as xml) c1 union all
	select cast('<v>23e2</v>' as xml) c1
 ) tt

ci1	ci1
1234	1234
NULL	NULL
NULL	NULL
NULL	12,3
NULL	2300

select cast('<v>1234</v>' 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;

Немного другой вариант, для поразмыслить
<code>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