CAST versus SUBSTRING

cast(<decimal> to string) VS. substring(<decimal> from 1) which is more expensive? can test it, but wanted opinions of the crowd first....thoughts? I know this looks like "too generic of a question", but consider it "all things equal" (if possible...) Thanks - Mark Scranton

mark.scranton@gmail.com wrote: > cast(<decimal> to string) VS. substring(<decimal> from 1) > > which is more expensive? can test it, but wanted opinions of the crowd > first....thoughts? I know this looks like "too generic of a question", > but consider it "all things equal" (if possible... I'm willing to place a bet: substring(<decimal> from 1) is more expensive. Namely by the cost of executing substring(<string> from 1). Substring is a string function. In order to operate on the decimal it needs to implicitly cast the decimal to the string first. Whether a cast is implicit or explicit won't make a difference. The work is the same. There is a possibility that substring(<string> from 1) is recognized as a no-op and eliminated by the SQL compiler. A long shot that can at best make them equal. There is no question about which one is more readable. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab IOD Conference http://www.ibm.com/software/data/ondemandbusiness/conf2006/

mark.scranton@gmail.com wrote: Couldn't get the substring to work, but compared <decimal col>::char(20) to <decimal col> || ''. I get that the explicit cast is a bit faster than the implicit cast and concatenate. That fits with Serge's analysis. Art S. Kagel > cast(<decimal> to string) VS. substring(<decimal> from 1) > > which is more expensive? can test it, but wanted opinions of the crowd > first....thoughts? I know this looks like "too generic of a question", > but consider it "all things equal" (if possible...) > > Thanks - > Mark Scranton >

Substring certainly should be Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 European Conference. Vienna, Austria. 2-6 October 2006 Visit http://www.iiug.org/conf for more information. -----Original Message----- From: mark.scranton@gmail.com [mailto:mark.scranton@gmail.com] Posted At: 15 August 2006 11:58 Posted To: comp.databases.informix Conversation: CAST versus SUBSTRING Subject: CAST versus SUBSTRING cast(<decimal> to string) VS. substring(<decimal> from 1) which is more expensive? can test it, but wanted opinions of the crowd first....thoughts? I know this looks like "too generic of a question", but consider it "all things equal" (if possible...) Thanks - Mark Scranton