Author |
Topic |
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-08-20 : 05:36:30
|
Hi everyoneI'm using MS-SQL2000 Pro. How do I replace more than one string in a column. If I had a column which indicates an item's status.Eg:ITEMSTATUS:1632486432I want to replace the numbers with words. I can do a replace(itemstatus, 16, 'IN'), but how do I do this for the other numbers then?There are only 5 numbers - I don't have another table which I can join to give a description for the numbers and I can't add a table 'cause it's against the license agreement of the software. So I need to replace the numbers with words but 5 times. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 05:57:55
|
Five update statements maybe?OrUPDATE Table1SET ItemStatus = CASE WHEN ItemStatus = '16' THEN 'IN' WHEN ItemStatus = '17' THEN 'OUT' WHEN ItemStatus = '18' THEN 'See yaa' WHEN ItemStatus = '19' THEN 'Hi there' WHEN ItemStatus IS NULL THEN 'Unknown' ELSE ItemStatus END E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-20 : 06:10:47
|
If it is just for display, thenSelect columns, ItemStatus = CASE WHEN ItemStatus = '16' THEN 'IN' WHEN ItemStatus = '17' THEN 'OUT' WHEN ItemStatus = '18' THEN 'See yaa' WHEN ItemStatus = '19' THEN 'Hi there' WHEN ItemStatus IS NULL THEN 'Unknown' ELSE ItemStatus ENDfrom Table1 MadhivananFailing to plan is Planning to fail |
|
|
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-08-20 : 06:12:11
|
Hi PesoNo, I'm not trying to update the table. I'm doing a select to do reporting. One of the columns in the select will have one of the numbers per row. I then want to replace the numbers in the select with more descriptive words.Like in select REPLACE(ITEMSTATUS, 16, 'DESTROYED') FROM ITEMS WHERE ITEMCODE = '0102'.That ensures that the number 16 shows as 'IN' in the returned results. But then I also ahve to ensure that the numbers 32, 38... also gets replaced with their respective words.16 = DESTROYED32 = IN48 = OUT64 = PERMOUTI want to do something like this: select itemstatus and replace 16 with 'DESTROYED' and 32 with 'IN' AND 48 WITH 'OUT' AND 64 with 'PERMOUT'. Nothing must be updated - it must merely display for easy reading. |
|
|
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-08-20 : 06:24:23
|
Hi Peso, thanks that's exactly what I wanted to do, just a simple select - BUT, now I'm getting an error:Error converting ITEMSTATUS to varchar from TINYINT.How can I convert the column to nvarchar? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-20 : 06:28:09
|
As I said, just use it in Select statementMadhivananFailing to plan is Planning to fail |
|
|
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-08-20 : 08:25:12
|
Hi guysPlease just help me to get this to work.When using:(CASE WHEN ItemStatus = '16' THEN 'IN') I get the "Error converting Integer to Nvarchar" error.I tried to use Case(Case when Itemstatus = '16' then 'IN' as nvarchar(50)) but then I get an error "Syntax error at 'at'". |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-20 : 08:31:15
|
quote: Originally posted by superhero Hi guysPlease just help me to get this to work.When using:(CASE WHEN ItemStatus = '16' THEN 'IN') I get the "Error converting Integer to Nvarchar" error.I tried to use Case(Case when Itemstatus = '16' then 'IN' as nvarchar(50)) but then I get an error "Syntax error at 'at'".
Can you post table structure and the full query you used?MadhivananFailing to plan is Planning to fail |
|
|
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-08-20 : 08:56:01
|
HERE'S THE CODE:RSITEMACTIVITYCUR.NewItemStatus is a TINYINTWhen I run the query, the exact error message is:Syntax error converting the varchar value 'IN' to a column of data type TINYINT.SELECT RSITEM.ItemCode, ISNULL(RSITEMACTIVITYCUR.Requestor, '-') AS Requestor, ItemStatus = CASE WHEN RSITEMACTIVITYCUR.NewItemStatus = '16' THEN 'IN' WHEN RSITEMACTIVITYCUR.NewItemStatus = '16' THEN 'DESTROYED' WHEN RSITEMACTIVITYCUR.NewItemStatus = '32' THEN 'IN' WHEN RSITEMACTIVITYCUR.NewItemStatus = '48' THEN 'OUT' WHEN RSITEMACTIVITYCUR.NewItemStatus = '64' THEN 'OUT' ELSE RSITEMACTIVITYCUR.NewItemStatus END, ISNULL(RSITEMACTIVITYCUR.WOItemComment, '-') AS WOComment, RSACTION.ActionDesc, RSITEMACTIVITYCUR.ActivityDtime, ISNULL(RSLOCATION.LocationCode, '-') AS LocationCode, ISNULL(RSWORKORDERCUR.WorkorderCode, '-') AS WorkOrderCode, RSACCOUNT.AccountName, RSACCOUNT.AccountCodeFROM RSITEMACTIVITYCUR INNER JOIN RSACTIVITYSET ON RSITEMACTIVITYCUR.SetTypeID = RSACTIVITYSET.SetTypeID INNER JOIN RSACTION ON RSACTION.ActionID = RSACTIVITYSET.ActionID INNER JOIN RSITEM ON RSITEM.ItemID = RSITEMACTIVITYCUR.ItemID LEFT OUTER JOIN RSLOCATION ON RSLOCATION.LocationID = RSITEMACTIVITYCUR.NewLocationID LEFT OUTER JOIN RSWORKORDERCUR ON RSWORKORDERCUR.WorkorderCurID = RSITEMACTIVITYCUR.WorkorderCurID INNER JOIN RSACCOUNT ON RSACCOUNT.AccountID = RSITEM.AccountIDWHERE (RSITEM.ItemCode = 'F00003231')ORDER BY RSITEMACTIVITYCUR.ActivityDtime |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 09:41:23
|
[code]SELECT RSITEM.ItemCode, ISNULL(RSITEMACTIVITYCUR.Requestor, '-') AS Requestor, ItemStatus = CASE WHEN RSITEMACTIVITYCUR.NewItemStatus = '16' THEN 'IN' WHEN RSITEMACTIVITYCUR.NewItemStatus = '16' THEN 'DESTROYED' WHEN RSITEMACTIVITYCUR.NewItemStatus = '32' THEN 'IN' WHEN RSITEMACTIVITYCUR.NewItemStatus = '48' THEN 'OUT' WHEN RSITEMACTIVITYCUR.NewItemStatus = '64' THEN 'OUT' ELSE CONVERT(VARCHAR, RSITEMACTIVITYCUR.NewItemStatus) END, ISNULL(RSITEMACTIVITYCUR.WOItemComment, '-') AS WOComment, RSACTION.ActionDesc, RSITEMACTIVITYCUR.ActivityDtime, ISNULL(RSLOCATION.LocationCode, '-') AS LocationCode, ISNULL(RSWORKORDERCUR.WorkorderCode, '-') AS WorkOrderCode, RSACCOUNT.AccountName, RSACCOUNT.AccountCodeFROM RSITEMACTIVITYCURINNER JOIN RSACTIVITYSET ON RSACTIVITYSET.SetTypeID = RSITEMACTIVITYCUR.SetTypeIDINNER JOIN RSACTION ON RSACTION.ActionID = RSACTIVITYSET.ActionIDINNER JOIN RSITEM ON RSITEM.ItemID = RSITEMACTIVITYCUR.ItemIDLEFT JOIN RSLOCATION ON RSLOCATION.LocationID = RSITEMACTIVITYCUR.NewLocationIDLEFT JOIN RSWORKORDERCUR ON RSWORKORDERCUR.WorkorderCurID = RSITEMACTIVITYCUR.WorkorderCurIDINNER JOIN RSACCOUNT ON RSACCOUNT.AccountID = RSITEM.AccountIDWHERE RSITEM.ItemCode = 'F00003231'ORDER BY RSITEMACTIVITYCUR.ActivityDtime[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
superhero
Yak Posting Veteran
52 Posts |
Posted - 2007-08-20 : 09:47:03
|
Thank you very much. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 09:53:09
|
This happens becuase the first record returned falls under the ELSE part in the CASE thingy, thus setting the "DATATYPE" for this column in complete query. E 12°55'05.25"N 56°04'39.16" |
|
|
|