Convert from nvarchar to numeric only if valid number-VAL command like MS Access

Question

Convert from nvarchar to numeric only if valid number-VAL command like MS Access

joseph419

SSC Enthusiast

Points: 112
More actions
May 4, 2020 at 1:20 pm

#3748592

Hi Folks,
I have been struggling with this and tried several different methods with no success. All I am trying to do is successfully create a view in which fields that are nvarchar or varchar can be converted to their appropriate field type. No matter what I do, be it using the isnumeric command or isdate command in a case statement so when true then convert/cast otherwise ignore but as soon as sql hits a specific record that cannot be converted to a number or date, it errors out on writing the table based on the view. Every time I change anything and re-run, because of the massive amount of data involved, it takes hours before it errors out sometimes. I know and often use the Val function in MS Access to easily convert be in nvarchar or varchar into number or date and using that command easily works and just ignores any value that it may get caught up on, but I find no equivalent command in SQL. Is there a way to go about this that will actually work and that won't take hours to run just to error out again? Please help, this is for local non-profit government ... too much money to upgrade anything right now, so I am just using SQL Server 2012 R2 on a Windows Server 2012 R2 box. Any help would be appreciated.

Thank you very much,

Joe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Use the TRY_CONVERT() or the TRY_CAST() function. They will return the value if it can convert/cast it, and will return NULL otherwise.

Drew

This reply was modified 4 years, 6 months ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Have you tried using TRY_CAST? I think that was available in 2012 ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

what exactly are you trying to do?

Is it a one time conversion or a view that will be available for querying on the fly going forward?

and do you have sample data and code you used?

try_convert mentioned above has issues (same as isdate or isnumeric) and it not necessarily the best/correct option to use based on your data - or maybe it could be used in conjunction with further validation.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

frederico_fonseca wrote:

what exactly are you trying to do?
Is it a one time conversion or a view that will be available for querying on the fly going forward?
and do you have sample data and code you used?
try_convert mentioned above has issues (same as isdate or isnumeric) and it not necessarily the best/correct option to use based on your data - or maybe it could be used in conjunction with further validation.

True DAT!!!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)