Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vimal
Starting Member
4 Posts |
 Posted - 2004-08-18 : 08:28:51
|
| Hi all, I have an attribute to store percentage of marks in a table. The values of this attribute may be 56,78,79,98,etc. My need is to replace each value of this attribute with a related word, for example 56 to be replaced with ABOVE55, 78 to be replaced with ABOVE75 and so on. The number of records would be around 700. How can I do that? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-18 : 08:45:09
|
| this should help:select 'above' + cast(78 - 78%5 as varchar)Go with the flow & have fun! Else fight the flow :) |
 |
|
|
cristian.babu
Starting Member
1 Post |
Posted - 2004-08-18 : 08:47:02
|
| If you want to do this in sql, you could build a mapping table with all the words you want to display, assuming that the range is known, and you don't have to build these words at runtime.try this:CREATE TABLE Map(MinValue int, MaxValue int, Word varchar(20))INSERT INTO Map VALUES(75, 84, 'ABOVE75')INSERT INTO Map VALUES(85, 89, 'ABOVE85')INSERT INTO Map VALUES(90, 99, 'ABOVE90')CREATE TABLE Val(Number int)INSERT INTO Val VALUES(78)INSERT INTO Val VALUES(79)INSERT INTO Val VALUES(91)SELECT V.Number, M.Word FROM Val V LEFT JOIN Map M ON V.Number BETWEEN M.MinValue AND M.MaxValuehth,Cristian Babu |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-18 : 12:40:36
|
| a bit revised:update t1 set t1.col1 = 'Above' + cast((t1.attribCol-1) - (t1.attribCol-1)%5 as varchar(20))from MyTable t1Where ...(t1.attribCol-1) is because if you have 80 it will say above 75, 81 will be above80if you want 80 to go into above80 use only t1.attribCol.Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
|
|
|
|
Want to replace numerical values with words