Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add build in TRY_CAST function [CORE5378] #5651

Open
firebird-issue-importer opened this issue Oct 17, 2016 · 9 comments
Open

Add build in TRY_CAST function [CORE5378] #5651

firebird-issue-importer opened this issue Oct 17, 2016 · 9 comments

Comments

@firebird-issue-importer

Submitted by: @livius2

Will be good to see TRY_CAST function.
It is similar to normal CAST
but when cast is not possible then TRY_CAST return NULL instead like normal CAST throw error

usage:

SELECT TRY_CAST('XYZ' AS INTEGER), TRY_CAST('123' AS INTEGER) FROM RDB$DATABASE

result:
NULL, 123

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

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, ...

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

Commented by: @asfernandes

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

Commented by: @livius2

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

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?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

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

....

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

Commented by: @asfernandes

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 17, 2016

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..

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant