Posted by Erland Sommarskog on 06/29/05 00:01

Axel (realraven2000@hotmail.com) writes:
> This is returned as ItemDesc='APEX 1/4" Hex Insert Pozidrive (3 1")'
> and truncates valuable information from ProductName although the
> resulting string is not even near the 60 character limit.
>
> In pseudo code my algorithm will look like:
> retrieve separate ProductName, Attrib1 - 3
> concatenate Attributes (each clipped to 30 chars) => Count length of
> resulting AttribString
> count length of ProductName
> if length(ProductName & AttribString) > 60 then
> clip(AttribString) to 30
> if length(ProductName & AttribString) > 60 then
> ProductName=left(ProductName,60-len(AttribString))
> endif
> endif
> This would probably be possible with T-SQL if I could put it into a
> function but I would not like to attempt it as part of a SELECT
> statement. The easiest is to leave this to the client and write a
> simple VBScript function. The other advantage is that since the
> presentation layer (website) has control on the way this string is
> displayed it might as well get control on how many chars are displayed,
> so this wraps it quite nicely.

Indeed putting it in a UDF could have performance implications.

The good news is that this is precisely what the CLR is good for in
SQL 2005. That is, in SQL 2005, you would write a scalar UDF in VB .Net
(or C#). This gives you the compiled performance of a 3GL language
as opposed the interpreted T-SQL. Also, in SQL2005, they have improved
performance on UDF calls, so even scalar UDFs in T-SQL are less
expensive in SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]