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

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

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

drew.allen SSC Guru Points: 77014 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 5 years, 6 months ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

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

frederico_fonseca SSCoach Points: 16279 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: 1004664 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)