SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conversion from varchar to strong data types


Conversion from varchar to strong data types

Author
Message
Drammy
Drammy
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 115
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?
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13018 Visits: 5478
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
Drammy
Drammy
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 115
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
Eugene Elutin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13018 Visits: 5478
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
Drammy
Drammy
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 115
Yeah, sorry should have said ud functions... :-)
Xedni
Xedni
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1891 Visits: 743
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.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43292 Visits: 19859
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search