Author |
Topic |
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-02-13 : 11:18:41
|
Hi there,Table like this:ID | Code | Abbrev |1 | 231 | BBV |2 | 232 | CCD |3 | 233 | BPG |...I already use code in where clause as:where code between 231 and 233here code column is varchar data type. What if I need to use Abbrev instead? I mean how can i get same effect as above clause with column of Abbrev? Thanks in advance. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-13 : 11:24:27
|
where Abbrev in ('BBV','CCD','BPG')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-02-13 : 12:24:17
|
Thanks visakh16.If the situation is:ID|Code | 1 | 231 | ...2 | 232 |3 | 233 | 4 | BBV | ...5 | CCD | ...and actually BBV=231, CCD=232, etc. can I use just number code to represent the abbrev letter by a new table with these two cols there referenced each other? Maybe I can use this:where code between 231 and 233 or code in ('BBV','CCD','BPG')But I want just use numbered Code to represent letter Code.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-13 : 12:26:38
|
yep...that where clause should work fine.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-02-13 : 12:29:23
|
Sorry I editted it even after you replied.What if I only want to use numbered Code to represent letter Code.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-13 : 12:39:40
|
quote: Originally posted by allan8964 Sorry I editted it even after you replied.What if I only want to use numbered Code to represent letter Code.Thanks.
then you need to have a separate mapping table where you map all codes (number as well as letter) to their number equivalents and then use it for serachingso it will have columnCode MappedCode-------------------------------BBV 231CCD 232BPG 233231 231232 232233 233..then use query likeSELECT t.FROM YourTable tJOIN MappingTable mON m.Code=t.CodeWHERE m.MappedCode BETWEEN 231 and 233 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
X002548
Not Just a Number
15586 Posts |
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-02-13 : 17:46:19
|
Thanks gents. The table is not there, I thought I need a mapping table but not sure how to manipulate the codes.Thanks visakh16 again. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-13 : 18:11:36
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|