Data type conversions

Question

Data type conversions

Gail Shaw

SSC Guru

Points: 1004504
More actions
July 7, 2006 at 9:34 am

#113567

Hi all
I ran across this situation earlier and I was wondering if anyone could tell me why things are like this.
I have a table (call it Test) with a column, called amount, that is declared varchar(25) Most of the values in this column are numeric, but there are some empty strings.
On one server, if I run the query
SELECT CreationDate , Amount
FROM Test WITH(NOLOCK)
WHERE CreationDate >= '2006/07/01'
AND CAST(Amount AS FLOAT) <= '10000'
I get an error, cannot convert varchar to float. On another server, the query runs fine. The two servers have the same version of SQL (8.00.818), the same collation and the same options set. I've checked the data in both and there are only empty strings and numbers
Anyone have any idea why the two servers would behave differently?
CREATE TABLE Test (
CreationDate DATETIME,
Amount VARCHAR(25)
)
CREATE
CLUSTERED INDEX idx_Test_CreationDate
ON Test (CreationDate)Any ideas will be appreciated. I'll only be able to answer on monday
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I can't believe this is coming from you! Generally you have the answers...

I assume you verified that "Amount" is never NULL. Also, why when you are converting the varchar to float, (and why float) are you then using a varchar to compare, ('10000')?

This will give you all sorts of results depending upon what you use or comment out:

-- I cannot put a CLUSTERED INDEX on a @TableVariable

DROP TABLE #Test

CREATE TABLE #Test( CreationDate datetime, Amount varchar(25))

CREATE CLUSTERED INDEX cidx_Test_CreationDate ON #Test( CreationDate)

INSERT INTO #Test

SELECT '2006/07/01', '20000' UNION ALL

SELECT '2006/08/01', '10000' UNION ALL

SELECT '2006/08/01', '9000' UNION ALL

SELECT '2006/09/01', '8000' UNION ALL

SELECT '2006/10/01', '9000' UNION ALL

SELECT '2006/11/01', '' UNION ALL

SELECT '2006/12/01', NULL

SELECT CreationDate, Amount

FROM #Test WITH(NOLOCK)

WHERE CreationDate >= '2006/07/01'

AND CAST( Amount AS integer)<= 10000

-- AND CAST( Amount AS float) <= '10000'

I wasn't born stupid - I had to study.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Gail,

I know you said you checked, but try this on the table that produces the error...

SELECT *

FROM Test

WHERE AMOUNT LIKE '%[^0-9]%'

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 3

SELECT CreationDate , Amount

FROM Test WITH(NOLOCK)

WHERE CreationDate >= '2006/07/01'

AND CAST(NULLIF(Amount, '') AS FLOAT) <= '10000'

_____________
Code for TallyGenerator

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The code posted is the query I found. I've tried converting to numeric, same result.

Thanks Sergiy, but that was the first thing I tried, along with case, and no joy.

Thanks Jeff, that caught it. Someone's entered an amount as '8,92', instead of '8.92'. also got values of '100000.', '786.2' and '250.000'. I didn't notice on friday because they looked like numeric values (and oddly enough are classified numeric. )

Now just got to convince management that it's a data issue, not a SQL query problem.

The problem appeared after I changed some indexes on the table, so currently, it's all my fault

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RyanRandall SSChampion Points: 13623 More actions · Answer 5

> and oddly enough are classified numeric

This issue looks like it was a consequence of the 'dodgy' isnumeric function. This article provides an 'isReallyNumeric' function, which can sometimes come in handy:

http://aspfaq.com/show.asp?id=2390

SELECT isnumeric('8,92'), dbo.isReallyNumeric('8,92')

/*results

----------- ----

1 0

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

stax68 SSChampion Points: 11711 More actions · Answer 6

--Had a look at the functions from http://aspfaq.com/show.asp?id=2390 linked in the above post,
--and made some minor alterations, using a fairly arbitrary set of conditions.
--These versions should be (to some degree) 1. more efficient, 2. more maintainable and 3. more useful.
--
--1: The CASE will exit as soon as a test is failed.
--2: Conditions are independent and can easily be identified, located, added, altered and removed
--3: Return value indicates which condition was (first) found to be unsatisfied.

create

function dbo.IsInvalidDecimal(@STR varchar(8000))

returns

int

begin

declare @retval int

select @retval =

case

when @STR like '%[^0-9 .-]%' then -1000 --valid characters restricted to: digit, space, point, minus

when @STR not like '%[0-9]%' then -1100 --must contain one digit

when @STR like '%[0-9.-]%-%' then -2000 --minus may not be preceded by another nonspace character

when @STR like '%[0-9.-] [0-9.-]%' then -3000 --only leading or trailing spaces allowed

when @STR like '%.%.%' then -4000 --only one point allowed

when @STR like '%.[^0-9]%' then -4100 --point may be followed only by a digit

when @STR like '%.' then -4200 --point may not be last character

else 0

end

return @retval

end

go

create function dbo.IsInvalidInteger(@STR varchar(8000))

returns

int

begin

declare @retval int

select @retval =

case

when @STR like '%[^0-9 -]%' then -1000 --valid characters restricted to: digit, space, minus

when @STR not like '%[0-9]%' then -1100 --must contain one digit

when @STR like '%[0-9.-]%-%' then -2000 --minus may not be preceded by another nonspace character

when @STR like '%[0-9.-] [0-9.-]%' then -3000 --only leading or trailing spaces allowed

else 0

end

return @retval

end

go

declare

@chars table

(

x int identity(1,1),

c varchar(32)

)

insert

@chars(c) values(char(8))

insert

@chars(c) values(char(9))

insert

@chars(c) values(char(10))

insert

@chars(c) values(char(11))

insert

@chars(c) values(char(12))

insert

@chars(c) values(char(13))

insert

@chars(c) values('.')

insert

@chars(c) values('-')

insert

@chars(c) values('0')

insert

@chars(c) values('1')

insert

@chars(c) values('2')

insert

@chars(c) values('9')

insert

@chars(c) values('€')

insert

@chars(c) values('€5.6')

insert

@chars(c) values('-€5.6')

insert

@chars(c) values('6^7')

insert

@chars(c) values('-6^7')

insert

@chars(c) values('-6^7+4')

insert

@chars(c) values('-6^7+4.3')

insert

@chars(c) values('-4.3')

insert

@chars(c) values('4.3')

insert

@chars(c) values('-4')

insert

@chars(c) values('4')

insert

@chars(c) values('0e30')

insert

@chars(c) values('0002')

insert

@chars(c) values('00e2')

insert

@chars(c) values('00f2')

insert

@chars(c) values('3.4')

insert

@chars(c) values(' -3.4 ')

insert

@chars(c) values(' -34 ')

insert

@chars(c) values(' -.4 ')

insert

@chars(c) values(' -. ')

insert

@chars(c) values(' -67. ')

insert

@chars(c) values('3.475')

insert

@chars(c) values('43243.404')

insert

@chars(c) values('43243.40.4')

insert

@chars(c) values(' 4324340.4 ')

insert

@chars(c) values(' 43243.40.4 ')

insert

@chars(c) values('43243. 40.4')

insert @chars(c) values('43243. 4')

insert

@chars(c) values(' 43243.4')

insert

@chars(c) values('43243 ')

insert

@chars(c) values(' -4324345334')

insert

@chars(c) values(' - 4324345 ')

insert

@chars(c) values(' -4324322339945334.')

insert

@chars(c) values('-432-43.4')

insert

@chars(c) values('3.e4')

insert

@chars(c) values('32')

insert

@chars(c) values('e.')

insert

@chars(c) values('$4.56')

insert

@chars(c) values('-$4.56')

insert

@chars(c) values('-1-')

insert

@chars(c) values('-1.4.5')

insert

@chars(c) values('1.4.5')

insert

@chars(c) values('+46')

insert

@chars(c) values('46+')

select

char_value = c,

is_numeric = isnumeric(c),

is_really_numeric = ~cast(dbo.isinvaliddecimal(c) as bit),

is_really_integer = ~cast(dbo.isinvalidinteger(c) as bit),

invalid_decimal = dbo.isinvaliddecimal(c),

invalid_integer = dbo.isinvalidinteger(c)

from

@chars

order

by c,

3,4 desc,5 desc

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

RyanRandall SSChampion Points: 13623 More actions · Answer 7

Nice work Tim.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

You can really tell that Time has had to deal with this before I share Ryan's sentiment... nice job!

Gail, thanks for the feedback... glad to have been of service. Tim's functions are spot on an fast as well as having a greatly expanded "condition of the number" return. They should help greatly in automating a cleanup of the current violations you have in the column.

Even before you have the column cleaned up, you can prevent new incorrect entries by using one or more of Tim's formulas as a constraint... something like this...

ALTER TABLE dbo.yourtable WITH NOCHECK

ADD CONSTRAINT CK_someconstraintname

CHECK (yourcolumnname NOT LIKE '%[^0-9]%')

You can add more NOT LIKE checks in the same constraint using "AND" in this case.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Thanks, those functions will be very useful. I know IsNumeric was shaky, I didn't realise it was this shaky.

Another interesting discovery from this - CAST('8,92' as FLOAT) throws an error, but CAST('8,92' AS MONEY) returns 892.00

Jeff, unfortunatly I can't change the column type, clean up the data or alter the table. It's a vendor product (won't mention which one) and changing the table structure might break the app. Changing the data might ne the same thing.

What I've done is 'fix' the query so it replaces ',' with '.' before doing the cast, and hope and pray it doesn't break again...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Understood... you're not really changing the column type... just making it so it won't take bad data. Most vendor apps can withstand this type of change even if they've made the mistake of using bound record sets in the app.

Should be able to do the data cleanup in the form of an UPDATE without causing the app any pain either although I'd certainly check for triggers before doing the cleanup.

I also know how management feels about 3rd part apps... first, they don't want you to touch them, and second, if anything goes wrong, it's YOUR fault, not the garbage app they bought. Sometimes, it better not to press your luck but it's a hard thing to just watch bad data exist when you know you can do something about and management thinks the "problem" was caused by you.

The reason why using commas in the conversion to MONEY is ok is because MONEY can be formatted with commas using CONVERT (3 different built-in formats for MONEY). No such conversion exists for FLOAT.

Thanks again for the feedback, Gail.

--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)