Reading a delimited text file I can’t find a way to handle ‘empty text’ on a conversion of a field
So for example,
Paul,1234.5678,9999.99
Jim,9999.99
On trying to convert the numeric values from string to decimal with CAST on column2, I get an error on conversion because of the ‘empty value’ on row 2
In SQLServer, I’d use a TRY_CAST/TRY_CONVERT which would return a NULL if not a number, or the proper casted value datatype if it is.
In the Dremio UI it uses CONVERT_TO_FLOAT, but that is undocumented and it has a problem. The values returned from the REST API on a conversion are horrid. A value of 46801122.6600 is returned as 4.680112266E7. I also need it to be a proper decimal.
What is the best way to achieve what I need?
I do realise that I could use
cast(case when AmountSecured=’’ then null else AmountSecured end as decimal(18,4))
which appears to work, but my goodness that adds a lot of noise to the query, and it’s horrid to read on the Dremio UI which lacks some configurability for reading …
so I’m looking for a nicer solution.
I get an error on conversion because of the ‘empty value’ on row 2
What’s the error exactly? The below seem to work for me on your example dataset (even with Null values).
cast(B as decimal(18,4)) as B
Thanks for your reply.
I’m going to hold my hands up on this one, cardinal sin of not analysing my actual source data enough, and assuming it was empty text screwing it up, and creating a dataset on the fly for the post.
What it actually was dodgy data further down and a figure literally of 99.xx
I see lots of CASE WHEN IS_xxxx wrappers in my future.
Again, apologies.
2 Likes
