Commented by: @dyemanov

create function try_cast_to_int(param varchar(32000))
returns int
as
begin
return cast(param as int);
when any do
return null;
end

Commented by: @livius2

Yes Dmitry - i know new psql function feature
but as you can see you must specify the type
may be not big overhead but, Date, Time, Timestamp, Numeric, ...

Commented by: @asfernandes

I'd say it makes no sense such function, but if you want you can do it manually.

Commented by: @livius2

i suppose that MSSQL will do not contain it if it is not usefull...

Commented by: @asfernandes

Why in the world someone would try to get a data and ignore it not knowing if it was null or something completely different?

Commented by: @livius2

e.g. external table data you try to import

CASE WHEN TRY_CAST(X AS INTEGER) THEN 'INTEGER'
WHEN TRY_CAST(X AS BIGINT) THEN 'BIGINT'
WHEN TRY_CAST(X AS NUMERIC(16, 2)) THEN 'NUMERIC'
WHEN TRY_CAST(X AS DOUBLE PRECISION) THEN 'DOUBLE'
ELSE 'Not number'
END

----------------------------

size of product e.g. you have data with:
58
59
XL
70
L
45

and you then can

select * FROM PRODUCTS WHERE TRY_CAST(SIZE AS Integer) IS NOT NULL
you got only numeric size

select * FROM PRODUCTS WHERE TRY_CAST(SIZE AS Integer) IS NULL
you got only textual size like XL, L, S ...

-----
and as your point of view "Why in the world someone would try to get a data and ignore it not knowing if it was null or something completely different?"
if someone try to fix database created by others and try to populate table from one field to e.g. two fields

....

Commented by: @asfernandes

I think you're asking for wrong function. Something like IS_TYPE would be much better for the problems you presented.

Commented by: @dyemanov

I tend to close this ticket as rejected. Otherwise I'm afraid we may end discussing try_substring_for_non_string(), try_add_non_numbers(), etc.

Commented by: @livius2

Dmitry - do not explode it
with one function like this you can do all your above ;-)

one more sample:

You design your query and you have it like:
select CAST(some concatenation as Integer) FROM SOME_SELECT

and you query run into error beacuse cast - how do you find the problem?
include some where clause to filter and try again? or simple

select try_CAST(some concatenation as Integer) FROM SOME_SELECT
and you see null values in problematic records

PS1> https://msdn.microsoft.com/en-us/library/hh974669.aspx
PS2> Your choice..