SQL Server Performance

Function: convert tinyint to varchar

Discussion in 'T-SQL Performance Tuning for Developers' started by abradley81, Jul 16, 2006.

  1. abradley81 New Member

    I'm looking to create a function which would take the a two digit number and return a varchar, but I can't seem to figure out all this syntax.

    For example:
    Pass it 25 and it would return "2 1/2"
    Pass it 37 and it would return "3 3/4
    Pass it 10 and it would return "1"

    Pretty simple if I did it in visual basic, but I can't get this t-sql stuff. Thanks.
  2. khtan New Member

    can you explain how to get
    25 to "2 1/2"
    37 to "3 3/4" etc ?


    KH
  3. FrankKalis Moderator

    I have no idea what you want to do with that, but see if this helps:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @a INT<br />SET @a = 25<br />SELECT <br /> CASE <br /> WHEN @a % 10 * 1.0 / 10 = 0 THEN LTRIM(@a / 10) + '' <br /> WHEN @a % 10 * 1.0 / 10 &gt; 0 AND @a % 10 * 1.0 / 10 &lt;= 0.25 THEN LTRIM(@a / 10) + '1/4' <br /> WHEN @a % 10 * 1.0 / 10 &gt; 0.25 AND @a % 10 * 1.0 / 10 &lt;= 0.5 THEN LTRIM(@a / 10) + ' 1/2'<br /> WHEN @a % 10 * 1.0 / 10 &gt; 0.5 AND @a % 10 * 1.0 / 10 &lt;= 0.75 THEN LTRIM(@a / 10) + ' 3/4'<br /> WHEN @a % 10 * 1.0 / 10 &gt; 0.75 AND @a % 10 * 1.0 / 10 &lt;= 1 THEN LTRIM(@a / 10) + ' 1' <br /> ELSE 'ERROR' END<br /><br /> <br />---------------- <br />2 1/2<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />You probably need to tweak this, but I'm sure you'll get the idea. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  4. FrankKalis Moderator

  5. abradley81 New Member

    What I have are numbers such as these:
    2
    2 1/2
    1 3/4
    1 1/2
    4
    5 3/4

    What I'm doing is storing numbers at a tinyint, but then converting them to look like this. So the tinyint 20 would equal "2". Numbers ending in 0 would be just a whole number (ie, 30 = "3"). Numbers ending in 5 would be 1/2 (25 = "2 1/2"). Numbers ending in 7 would be 3/4 (17 = "1 3/4")

    The only possible numbers I need to convert are:
    10
    15
    17
    20
    25
    27
    30
    35
    37
    40
    45
    47
    50

    I'm doing this because I doing want to make the numbers a real/float, just for small decimals (and I want it to output fractions, not decimals). And I don't want them to be varchars like this because then I can't sort them (probably would work, but slower).

    My thought is that it would be quicker to have the database return "1 3/4" as a varchar then having each page do the conversion using VB.NET? Am I right or doesn't it matter? Either way this conversion will need to be done on multiple pages and I don't feel like attaching the function to every single output on the site.

    If I only have 13 possible numbers that need conversion, should I just make a case statement with those exact 13, or do some string manipulation and see what the first and second numbers are and convert from there?

    Thanks
  6. FrankKalis Moderator

    I might be wrong, but the script I've posted should do the job for you. According to you possible range of allowed values it can be further simplified to:


    DECLARE @a INT
    SET @a = 47
    SELECT
    CASE
    WHEN @a % 10 = 0 THEN LTRIM(@a / 10) + ''
    WHEN @a % 10 = 5 THEN LTRIM(@a / 10) + ' 1/2'
    WHEN @a % 10 = 7 THEN LTRIM(@a / 10) + ' 3/4'
    ELSE 'ERROR' END



    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs

Share This Page