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 12»»

Extracting a Numeric Reference from a VARCHAR field Expand / Collapse
Author
Message
Posted Wednesday, November 19, 2008 8:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 1:05 AM
Points: 488, Visits: 400
Comments posted to this topic are about the item Extracting a Numeric Reference from a VARCHAR field
Post #605532
Posted Thursday, November 20, 2008 6:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:51 PM
Points: 2,628, Visits: 760
declare @myTab table( ExtRef varchar(9), IntRef decimal(38,19))
insert into @myTab(ExtRef,IntRef) values ('1',0)
insert into @myTab(ExtRef,IntRef) values ('2.1',0)
insert into @myTab(ExtRef,IntRef) values ('A',0)
update @myTab
set IntRef = case when isnumeric(ExtRef) = 1 then ExtRef else -1
--(A)
end

update @myTab
set IntRef = case when isnumeric(ExtRef) = 1 then ExtRef else -1
--(A)
end


Confused here --(A) is a comment, yes?

The cast to decimal would be required otherwise a failure converting varchar to decimal.


Jamie
Post #605765
Posted Thursday, November 20, 2008 6:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:05 PM
Points: 1,816, Visits: 403
I'm using SQL Server 2008.

To get the script to work for decimal numbers like 35.12, I had to change
when isnumeric(ExtRef) = 1 then ExtRef
to
when isnumeric(ExtRef) = 1 then cast(ExtRef as decimal)

After I made that change, I was able find the problem that decimal data with dollar signs are considered numeric but can't be cast as decimals.

Jim
Post #605770
Posted Thursday, November 20, 2008 6:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:51 PM
Points: 2,628, Visits: 760
Jim,

Out of the box SQL 2005

declare @myTab table( ExtRef varchar(9), IntRef decimal(38,19))
insert into @myTab(ExtRef,IntRef) values ('$1.00',0)
insert into @myTab(ExtRef,IntRef) values ('2.1',0)
insert into @myTab(ExtRef,IntRef) values ('A',0)
update @myTab
set IntRef = case when isnumeric(ExtRef) = 1 then cast(ExtRef as decimal(38,19))else cast(-1 as decimal(38,19))
--(A)
end

Good catch on the dollar sign!


Jamie
Post #605772
Posted Thursday, November 20, 2008 7:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:53 AM
Points: 1,182, Visits: 1,949
Additionally,

1. It doesn't properly support a legitimate value of -1 as -1 is being used to indicate that the value is not numeric.

2. An explicit CAST is needed in the assignment part ("THEN") of the CASE statement as for some quirky reason, a value of '123.45' cannot be implicitly converted to DECIMAL(38,19) when it is part of a CASE statement. Error: Conversion failed when converting the varchar value '123.45' to data type int. Yet a direct assignment (IntRef = ExtRef) works fine! (SQL Server 2005 SP2)



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #605787
Posted Thursday, November 20, 2008 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:19 AM
Points: 20, Visits: 192
I see the exact same behavior with 2000.
Post #605813
Posted Thursday, November 20, 2008 7:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 10:27 AM
Points: 3,357, Visits: 675
JohnG -
If you cast the -1 to Decimal(38,19), you won't get the error on 123.45

Great question.



Post #605820
Posted Thursday, November 20, 2008 10:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 942, Visits: 1,050
I liked the question; I didn't think about it when I answered it much (so I got it wrong ). The SQL syntax looked right, but needed to read it a bit more carefully I guess.

I just got confused by -- (A); I wasn't sure what was being implied by that when I read the options available to me.



---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing.


How to ask for help .. Read Best Practices here.
Post #605965
Posted Thursday, November 20, 2008 12:50 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:43 AM
Points: 1,477, Visits: 2,100
Good one!

Jason Shadonix
MCTS, SQL 2005
Post #606062
Posted Monday, November 24, 2008 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,794, Visits: 8,009
JohnG (11/20/2008)
Additionally,

1. It doesn't properly support a legitimate value of -1 as -1 is being used to indicate that the value is not numeric.

2. An explicit CAST is needed in the assignment part ("THEN") of the CASE statement as for some quirky reason, a value of '123.45' cannot be implicitly converted to DECIMAL(38,19) when it is part of a CASE statement. Error: Conversion failed when converting the varchar value '123.45' to data type int. Yet a direct assignment (IntRef = ExtRef) works fine! (SQL Server 2005 SP2)


Hi John,

Well, your first point is by design. If the design calls for -1 being used to represent incorrect format, you can't blame the code for using -1 for two purposes. Apparently, the assumption here is that -1 will never be a real value in the data. :)

WRT the second point - the reason is not "quirky" at all. The data type of the CASE is always equal to a data type used in one of the WHEN clauses or the ELSE clause, with the choice being determined by the rules of precedence. In the posted code, data types used were varchar(9) (from the ExtRef column) and int (from the constant -1, as a constant with no decimal point and within the range of integers is always considered to be int). Of these, int has the highest precedence, so the varchar is converted. And after that, the result of the CASE is converted to decimal(38,19) for the purpose of assiging it to the IntRef column.
I'll gladly admit that I missed this double conversion myself. Fortunately I did not lose a point over it as I am well aware of the limitations of ISNUMERIC(), but it wasn't until I read the comments here that I noticed the code would choke on "really" valid decimal numbers as well due to the hidden conversion to integer.

Good question!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #607355
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse