Conversion from varchar to strong data types

Question

Conversion from varchar to strong data types

Drammy

Old Hand

Points: 335
More actions
March 21, 2013 at 6:09 am

#270070

Hi all,
There are a few databases I work with that have been designed where varchar columns are used to store what actually displays on the front end as Ints, Decimals, Varchars, Datetimes, checkboxes.
I often have to write integrations with these databases bringing data in and prefer to validate the data whilst loading from the staging tables.
I have seen allsorts of values being passed into the staging tables that will load into the target database because the columns are all varchars but the values don't display on the front end because the app actively filters bad values out.
Poor design, I know, the designers have their reasons and I don't want to really get into all that on this thread.
What I would like to do is for my validation scripts to warn up front of potentially invalid datatypes. My problem is that forexample the ISNUMERIC() function return 1 for the value ',1234' but a CONVERT(NUMERIC, ',1234') or CAST(',1234' AS NUMERIC) will fail with a "Error converting data type varchar to numeric).
I've been trying to locate a set of reliable datatype testing functions that will reliably determine if a varchar can be converted to a given data type or not.
Does anyone know of any?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

ISNUMERIC doesn't guarantees that the value is of NUMERIC datatype.

It's only the indicator if the value can be converted into one of many SQL Server numeric data types (exact or approximate ones).

Your sample is easily converted into money:

SELECT CAST(',1234' AS MONEY)

Actually, MONEY is one of the least "restrictive" numeric datatype, as a lot of complete rubbish can still be converted into it:

SELECT CAST('$' AS MONEY)

SELECT CAST('.' AS MONEY)

SELECT CAST(',' AS MONEY)

SELECT CAST('.,' AS MONEY)

SELECT CAST(',,,,.' AS MONEY)

SELECT CAST('-,.' AS MONEY)

All the above will work, therefore, ISNUMERIC will return true for all of the above...

SQL2012 offers the new TRY_CONVERT function, but pre-2012 there is nothing "out of the box" for this sort of thing.

However, you can create your own function (SQL or CLR) to do so, depending on exact data types you want to validate.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Drammy Old Hand Points: 335 More actions · Answer 2

Thanks for the quick reply.

And thanks for the suggestion with the money data type.

I understand what ISNUMERIC is used for; that was just an example.

I was just trying to save time and was hoping someone would know of existing functions in the community that produce the same results as TRY_CONVERT but for 2005, 2008 and 2012...

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Drammy (3/21/2013)
Thanks for the quick reply.
And thanks for the suggestion with the money data type.
I understand what ISNUMERIC is used for; that was just an example.
I was just trying to save time and was hoping someone would know of existing functions in the community that produce the same results as TRY_CONVERT but for 2005, 2008 and 2012...

If 2005 or 2008 had it, you would probably managed to google it out, but...

No, there is none in pre-2012 versions.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Drammy Old Hand Points: 335 More actions · Answer 4

Yeah, sorry should have said ud functions... 🙂

Xedni SSCertifiable Points: 6951 More actions · Answer 5

Yeah, try_convert sure took it's time getting here. We don't have 2012+ on any production boxes either so it's not an option.

One trick to improve isnumeric() functionality is prefix '0e' to whatever string you're converting. I'll admit I haven't tested this thoroughly myself but the idea is that the 'e' tricks SQL into treating the string as hexidecimal and thus changes the rules it uses to parse the value.

declare

@int varchar(30) = '1234',

@notInt varchar(30) = ',1234'

select

PrefixInt = isnumeric('0e' + @int),

PrefixNotInt = isnumeric('0e' + @notInt),

NormalInt = isnumeric(@int),

NormalNotInt = isnumeric(@notInt)

Here's another forum post about the general idea.

http://www.sqlservercentral.com/Forums/Topic117734-23-1.aspx

Executive Junior Cowboy Developer, Esq.[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

Why don't you use some TRY...CATCH... blocks?

Here's an example:

DECLARE @i int, @Error varchar(100) = ''

BEGIN TRY

SET @i = 'a'

END TRY

BEGIN CATCH

SET @Error = @Error + CHAR(13) + ERROR_MESSAGE()

END CATCH

IF LEN(@Error) > 0

SELECT 'Conversion errors: ' + @Error

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2