Function: convert tinyint to varchar | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Function: convert tinyint to varchar

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.
can you explain how to get
25 to "2 1/2"
37 to "3 3/4" etc ?
KH
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>
…btw, I would rather use two columns for that measure. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
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
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
]]>