Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion from varchar to strong data types Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 2:37 PM
Points: 19, Visits: 112
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?
Post #1433747
Posted Thursday, March 21, 2013 8:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433800
Posted Thursday, March 21, 2013 8:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 2:37 PM
Points: 19, Visits: 112
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...
Post #1433808
Posted Thursday, March 21, 2013 8:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433812
Posted Thursday, March 21, 2013 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 2:37 PM
Points: 19, Visits: 112
Yeah, sorry should have said ud functions...
Post #1433830
Posted Tuesday, July 29, 2014 10:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 151, Visits: 453
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.
Post #1597407
Posted Tuesday, July 29, 2014 4:25 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 4,041, Visits: 9,187
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1597522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse