Changing Integer to Text - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Changing Integer to Text

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-31 : 09:52:08

Jim writes "I am writing a database that uses Javascript to input "Integers" into a specific field. What I am tying to do is when I run the SQL query (which pulls those numbers) I would like to replace the numbers with Text Values. Example:

Record ID: 1
Vendor ID: 1
Type ID: 5

The record id is fixed but I would like to print a HTML report that replaces the other ID numbers with TEXT Outputs.

Vendor ID: 1 1=Cisco
2=Bay Networks

Type ID: 5 1=Switch
5=Router

Is this possible??

Thanks!"

izaltsman
A custom title

1139 Posts

Posted - 2001-12-31 : 10:21:38

Do you have a Vendors table and EquipmentTypes table that contain names/descriptions associated with codes you are using? If you have those tables, then all you have to do is join to them:


SELECT 
    mt.RecordId
    , v.Name
    , t.Name
FROM 
   mytable mt 
   INNER JOIN vendors v ON mt.vendorid = v.vendorid
   INNER JOIN EquipmentTypes t ON mt.typeid = t.typeid

Now, if you don't have those tables, you could do all of your "decoding" inside your queries:


SELECT RecordId, 
       CASE VendorID WHEN 1 THEN 'Cisco' WHEN 2 THEN 'Bay Networks' ELSE 'Unknown' END, 
       CASE TypeID WHEN 1 THEN 'Switch' WHEN 5 THEN 'Router' ELSE 'Unknown' END
FROM mytable

But this approach is very inflexible -- every time you decide to add a new vendor or equipment type, you'll have to change your query.

Nazim
A custom title

1408 Posts

Posted - 2001-12-31 : 10:23:43

you can use case in you select to pull the data, something like this should help u.

CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

for your case

select case vendor_id when 1 then 'CISCO' when 2 then 'BAY Networks'
end,
case type_id when 1 then 'Switch' when 2 then 'Switch' when 5
then 'Router'
end
from tablename

Just Realized ,beaten by Illya by five seconds

HTH

Edited by - Nazim on 12/31/2001 10:35:38

Edited by - Nazim on 12/31/2001 10:36:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources