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
 Transact-SQL (2000)
 REPLACE()

Author  Topic 

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-20 : 05:36:30
Hi everyone

I'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:
16
32
48
64
32

I 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?

Or
UPDATE	Table1
SET 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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 06:10:47
If it is just for display, then
Select 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
END
from Table1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-20 : 06:12:11
Hi Peso

No, 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 = DESTROYED
32 = IN
48 = OUT
64 = PERMOUT

I 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.
Go to Top of Page

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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 06:28:09
As I said, just use it in Select statement

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-20 : 08:25:12
Hi guys

Please 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'".

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 08:31:15
quote:
Originally posted by superhero

Hi guys

Please 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?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-20 : 08:56:01
HERE'S THE CODE:
RSITEMACTIVITYCUR.NewItemStatus is a TINYINT
When 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.AccountCode
FROM 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.AccountID
WHERE (RSITEM.ItemCode = 'F00003231')
ORDER BY RSITEMACTIVITYCUR.ActivityDtime
Go to Top of Page

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.AccountCode
FROM RSITEMACTIVITYCUR
INNER JOIN RSACTIVITYSET ON RSACTIVITYSET.SetTypeID = RSITEMACTIVITYCUR.SetTypeID
INNER JOIN RSACTION ON RSACTION.ActionID = RSACTIVITYSET.ActionID
INNER JOIN RSITEM ON RSITEM.ItemID = RSITEMACTIVITYCUR.ItemID
LEFT JOIN RSLOCATION ON RSLOCATION.LocationID = RSITEMACTIVITYCUR.NewLocationID
LEFT JOIN RSWORKORDERCUR ON RSWORKORDERCUR.WorkorderCurID = RSITEMACTIVITYCUR.WorkorderCurID
INNER JOIN RSACCOUNT ON RSACCOUNT.AccountID = RSITEM.AccountID
WHERE RSITEM.ItemCode = 'F00003231'
ORDER BY RSITEMACTIVITYCUR.ActivityDtime[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-20 : 09:47:03
Thank you very much.
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -