|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why can't a LEN argument be passed to a CAST call?Can someone tell me why this creates an error:
declare @int int set @int = 55 select cast(@int as char(Len(@int))) CHAR takes an integer argument, and LEN returns an integer, so why should it fail? Probably something about set-based logic that eludes me, that would work fine in procedural code. Bob Graham Your statement would evaluate as
SELECT CAST(55 as CHAR(LEN(55))) which doesn't make sense to me. What are you trying to do? Stu For use in a stored procedure where the number being converted to
string isn't known ahead of time, and I need to pad it with 0's (using Replicate) to be a certain length. Casting to VarChar instead of Char works fine, I was just curious why the above wouldn't work. >> I need to pad it with 0's (using Replicate) to be a certain length.<< Are you aware that you are supposed to do formatting in the front endand NEVER in the database?? That is ** the** fundamental idea behind tiered architectures -- a far more general principle of programming than SQL. --CELKO-- wrote:
> >> I need to pad it with 0's (using Replicate) to be a certain length.<< Is this *the* Joe Celko??? or an imitation? :-)> > Are you aware that you are supposed to do formatting in the front end > and NEVER in the database?? That is ** the** fundamental idea behind > tiered architectures -- a far more general principle of programming > than SQL. Seriously, the reason for this is that I'm being asked to combine data from tables that were never meant to be viewed together. So we're adding a letter at the front of the identity column from several different tables and displaying them padded so they will sort alphabetically correctly, ie: G00233 will be *after* G00078, not before. On, a more genreal note, the power of any computer language is what you can do that wasn't foreseen. Otherwise the languages would be a lot smaller and none of us would have jobs. I think. Now that I have this working right, it's leaps and bounds faster than having my front-end program iterate through all the rows to prepend characters. Bob Graham A possible solution would be to dynamically build the SP as a temporary SP
(with the required length for Char() or as a String and EXEC it. -- Show quoteSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF <rvgrahamsevaten***@sbcglobal.net> wrote in message news:1131243410.321488.286440@g49g2000cwa.googlegroups.com... > > --CELKO-- wrote: >> >> I need to pad it with 0's (using Replicate) to be a certain length.<< >> >> Are you aware that you are supposed to do formatting in the front end >> and NEVER in the database?? That is ** the** fundamental idea behind >> tiered architectures -- a far more general principle of programming >> than SQL. > > Is this *the* Joe Celko??? or an imitation? :-) > > Seriously, the reason for this is that I'm being asked to combine data > from tables that were never meant to be viewed together. So we're > adding a letter at the front of the identity column from several > different tables and displaying them padded so they will sort > alphabetically correctly, ie: G00233 will be *after* G00078, not > before. > > On, a more genreal note, the power of any computer language is what you > can do that wasn't foreseen. Otherwise the languages would be a lot > smaller and none of us would have jobs. I think. > > Now that I have this working right, it's leaps and bounds faster than > having my front-end program iterate through all the rows to prepend > characters. > > Bob Graham > > Are you aware that you are supposed to do formatting in the front end You can not be so definitive.> and NEVER in the database?? That is ** the** fundamental idea behind > tiered architectures -- a far more general principle of programming > than SQL. How many systems have you had a hand in? Do you think pumping 1 million rows out to a middle tier / front end when you just need page 5 or 'x' scales? No, you would do it in SQL Server and only return the rows necessary. Yet again you fail to understand real world concepts like scalability, user front end performance and maintainability. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1131242397.690583.64170@z14g2000cwz.googlegroups.com... >>> I need to pad it with 0's (using Replicate) to be a certain length.<< > > Are you aware that you are supposed to do formatting in the front end > and NEVER in the database?? That is ** the** fundamental idea behind > tiered architectures -- a far more general principle of programming > than SQL. > Stu (stuart.ainswo***@gmail.com) writes:
> Your statement would evaluate as No, len(55) returns 2, as 55 is implicitly converted to the string> > SELECT CAST(55 as CHAR(LEN(55))) '55' and then it goes downhill from there. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp On 5 Nov 2005 09:35:16 -0800, rvgrahamsevaten***@sbcglobal.net wrote:
>Can someone tell me why this creates an error: Hi Bob,> >declare @int int >set @int = 55 > >select cast(@int as char(Len(@int))) > > >CHAR takes an integer argument, and LEN returns an integer, so why >should it fail? > >Probably something about set-based logic that eludes me, that would >work fine in procedural code. Nothing to do with set-based logic. The datatype char(..) and varchar(..) allow only an integer VALUE for the character's length, not an expression. DECLARE @test char(3+3) fails as well. As does DECLARE @i int SET @i = 6 DECLARE @test char(@i) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics