Implicit conversion in SQL Server
-
The project on which I've been working for the last two years copies data between two third-party products. It's in production and basically in maintenance at this point. These products deal, in part, with the street addresses of clients. One of them stores the house number as a VARCHAR(20), which is good. The other stores it as an integer, which is not. I think you can guess what happened this morning, but you'd only be partly correct. Yes, a street address like "123 1/2" was entered in the first system, but it was properly thrown back by the second. The bug was that my process then wanted to check to see the record in the second system, so I have an SQL statement to do something like:
SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON A.number = B.number ...(These are SQL Server databases) I just wasn't thinking clearly enough to realize that I was comparing a VARCHAR(20) to an integer. And if I had, I probably would have assumed that the integer would be converted to a VARCHAR, but nooooo, SQL Server tried to implicitly convert the "123 1/2" to an integer, and so threw:SQL: 1 Native: 245(F5) Text: Syntax error converting the varchar value '123 1/2' to a column of data type int.So I altered the SQL to be:SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON **CAST(**A.number **AS VARCHAR(20))** = B.number ...Of course, any help on a more efficient solution would be appreciated. -
I just don't understand why do you use int values if something with a "/" is valid. [number] should be a varchar if something like "123 1/2" is valid, don't you think?
I don't, it's a third-party system's database, I can't change it. I'm always railing against they who use integers for non-numbers that can look like numbers: Zip codes, phone numbers, etc.
-
I don't, it's a third-party system's database, I can't change it. I'm always railing against they who use integers for non-numbers that can look like numbers: Zip codes, phone numbers, etc.
-
Ok, but so it is impossible that the db stores such value as "123 1/2" so, now, how do you even bother searching for it when you know it is not a valid value? I'm just trying to understand why do you do all this if the field is an int value.
I search for all of them at once, not individually.
-
I search for all of them at once, not individually.
Don't make sense to me, sorry. Maybe there is an explanation but I don't see it. What result do you expect from that query? SELECT * FROM table WHERE intValue = '123 1/2' makes no sense to me but... Maybe "SELECT * FROM table WHERE intValue IN (1,2,3)" there you get them all at once.
-
I search for all of them at once, not individually.
-
The project on which I've been working for the last two years copies data between two third-party products. It's in production and basically in maintenance at this point. These products deal, in part, with the street addresses of clients. One of them stores the house number as a VARCHAR(20), which is good. The other stores it as an integer, which is not. I think you can guess what happened this morning, but you'd only be partly correct. Yes, a street address like "123 1/2" was entered in the first system, but it was properly thrown back by the second. The bug was that my process then wanted to check to see the record in the second system, so I have an SQL statement to do something like:
SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON A.number = B.number ...(These are SQL Server databases) I just wasn't thinking clearly enough to realize that I was comparing a VARCHAR(20) to an integer. And if I had, I probably would have assumed that the integer would be converted to a VARCHAR, but nooooo, SQL Server tried to implicitly convert the "123 1/2" to an integer, and so threw:SQL: 1 Native: 245(F5) Text: Syntax error converting the varchar value '123 1/2' to a column of data type int.So I altered the SQL to be:SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON **CAST(**A.number **AS VARCHAR(20))** = B.number ...Of course, any help on a more efficient solution would be appreciated. -
How does this third part system handle alpha-numeric house numbers? 4B The Street 2A The Avenue Or, for that matter, people who have house names rather than numbers The Spindles, Avenue Road.
-
The project on which I've been working for the last two years copies data between two third-party products. It's in production and basically in maintenance at this point. These products deal, in part, with the street addresses of clients. One of them stores the house number as a VARCHAR(20), which is good. The other stores it as an integer, which is not. I think you can guess what happened this morning, but you'd only be partly correct. Yes, a street address like "123 1/2" was entered in the first system, but it was properly thrown back by the second. The bug was that my process then wanted to check to see the record in the second system, so I have an SQL statement to do something like:
SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON A.number = B.number ...(These are SQL Server databases) I just wasn't thinking clearly enough to realize that I was comparing a VARCHAR(20) to an integer. And if I had, I probably would have assumed that the integer would be converted to a VARCHAR, but nooooo, SQL Server tried to implicitly convert the "123 1/2" to an integer, and so threw:SQL: 1 Native: 245(F5) Text: Syntax error converting the varchar value '123 1/2' to a column of data type int.So I altered the SQL to be:SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON **CAST(**A.number **AS VARCHAR(20))** = B.number ...Of course, any help on a more efficient solution would be appreciated.Could you use a view here? Instead of having to remember to do the cast every time you compare, you would then just use the view.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
How does this third part system handle alpha-numeric house numbers? 4B The Street 2A The Avenue Or, for that matter, people who have house names rather than numbers The Spindles, Avenue Road.
It doesn't. A very poor design.
-
Could you use a view here? Instead of having to remember to do the cast every time you compare, you would then just use the view.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
I think that would be less efficient. I could use a stored procedure or function that returns a table, but I doubt it's worth the trouble.
-
I think that would be less efficient. I could use a stored procedure or function that returns a table, but I doubt it's worth the trouble.
If you're not doing repeated inserts or updates, it may be worth extending the table with a computed column which performs the cast for you.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
I think that would be less efficient. I could use a stored procedure or function that returns a table, but I doubt it's worth the trouble.
-
If you're not doing repeated inserts or updates, it may be worth extending the table with a computed column which performs the cast for you.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
I CAN'T CHANGE THE TABLE!!! It's a third-party database!
-
I CAN'T CHANGE THE TABLE!!! It's a third-party database!
OK - NO NEED TO SHOUT!!! You haven't really said why you can't change the database. Just because it's somebody else's database doesn't necessarily mean that you can't change it. I've worked on plenty of systems where somebody elses tables/databases have been added to in order to add value. You may have good reasons why you can't change it, but don't dismiss things out of hand.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
OK - NO NEED TO SHOUT!!! You haven't really said why you can't change the database. Just because it's somebody else's database doesn't necessarily mean that you can't change it. I've worked on plenty of systems where somebody elses tables/databases have been added to in order to add value. You may have good reasons why you can't change it, but don't dismiss things out of hand.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
While that's true... "Don't do to others' databases what you wouldn't have done to yours." Adding tables and views isn't (usually) as bad as adding columns, but one of my predecessors here added tables to one of the third-party databases and caused the backup to quietly fail and no one noticed for months. On this particular database with the int field, I can't do any kind of write without crashing the server.
-
While that's true... "Don't do to others' databases what you wouldn't have done to yours." Adding tables and views isn't (usually) as bad as adding columns, but one of my predecessors here added tables to one of the third-party databases and caused the backup to quietly fail and no one noticed for months. On this particular database with the int field, I can't do any kind of write without crashing the server.
Fair enough, but it sounds like the DBAs need to be requesting an upgrade to the server.:-D It sounds as though you are having some serious space issues.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
I don't, it's a third-party system's database, I can't change it. I'm always railing against they who use integers for non-numbers that can look like numbers: Zip codes, phone numbers, etc.
This has also bothered me so much, people trying to use a number column for stuff like that. I always use the following, if you want to do stuff with the number (like average, adding, ...) THEN you store as number. Like it would be usefull to have the average of a postal code...
-
The project on which I've been working for the last two years copies data between two third-party products. It's in production and basically in maintenance at this point. These products deal, in part, with the street addresses of clients. One of them stores the house number as a VARCHAR(20), which is good. The other stores it as an integer, which is not. I think you can guess what happened this morning, but you'd only be partly correct. Yes, a street address like "123 1/2" was entered in the first system, but it was properly thrown back by the second. The bug was that my process then wanted to check to see the record in the second system, so I have an SQL statement to do something like:
SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON A.number = B.number ...(These are SQL Server databases) I just wasn't thinking clearly enough to realize that I was comparing a VARCHAR(20) to an integer. And if I had, I probably would have assumed that the integer would be converted to a VARCHAR, but nooooo, SQL Server tried to implicitly convert the "123 1/2" to an integer, and so threw:SQL: 1 Native: 245(F5) Text: Syntax error converting the varchar value '123 1/2' to a column of data type int.So I altered the SQL to be:SELECT ... FROM secondsystem A INNER JOIN firstsystem B ON **CAST(**A.number **AS VARCHAR(20))** = B.number ...Of course, any help on a more efficient solution would be appreciated.There is always ISNUMERIC if you want to compare two numbers. Although I think are non-deterministic, for sure ISNUMERIC('123 1/2') will evaluate to false. So:
SELECT ... FROM secondsystem A WITH(NOLOCK) INNER JOIN firstsystem B WITH(NOLOCK) ON CASE WHEN (ISNUMERIC(A.number) = 0 OR ISNUMERIC(b.number) = 0) THEN -1 ELSE CASE WHEN CAST(A.number AS INT) = CAST(B.number AS INT) THEN 1 ELSE 0 END END = 1 ...So the ON clause will evaluate to -1 if either A.number or B.number are non-numeric, when both are but values don't match is 0, and when are both integers and equal evaluates to 1, which is the pass condition for ON clause to consider the JOIN. Can be used a scalar function, too, to do the same:CREATE FUNCTION EqualNumbers(@A nvarchar(50), @B nvarchar(50)) RETURNS BIT AS BEGIN IF @A IS NULL RETURN 0 IF @B IS NULL RETURN 0 IF ISNUMERIC(@A) = 0 RETURN 0 IF ISNUMERIC(@B) = 0 RETURN 0 IF CAST(@A AS INT) <> CAST(@B AS INT) RETURN 0 RETURN 1 ENDand now the join is simplySELECT ... FROM secondsystem A WITH(NOLOCK) INNER JOIN firstsystem B WITH(NOLOCK) ON .dbo.EqualNumbers(A.number, B.number) <> 0Probably the direct JOIN will be quicker than the function. If the comparation has to be between two (n)varchar values, use a convert to (n)varchar for values to be compared, or whatever logic meaning the fields to be compared have (address is a prime context for this). You can encounter later money value in A stored as varchars and money in B, so you'll have also to consider, if converting to varchar, possible locale issues when casting etc. etc. etc.