updating a character of a column

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)

updating a character of a column

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2001-10-08 : 11:45:49

Hi guys,
I'm trying to update a column in all rows of a table, replacing the last character with a character I find in another table. I've been fooling around with this all morning and doing searches but I can't quite seem to find the solution.

Here is my test table


CREATE TABLE [user_test] (
	[amount] [char] (12) NULL 
) ON [PRIMARY]
GO

and I seeded it thusly:


declare @x as int
select @x = 1

While @x < 2500
begin
  insert into user_test
              (amount)
values (convert(char(12),(convert(DECIMAL(10,2),@x * 3.14159))))
select @x = @x+1
end

I need to replace the last digit in the above table with the match found in the following small table


CENT_VALUE POS_SYMBOL NEG_SYMBOL 
---------- ---------- ---------- 
0          {          }
1          A          J
2          B          K
3          C          L
4          D          M
5          E          N
6          F          O
7          G          P
8          H          Q
9          I          R

This is the last piece of code I futzed with

update user_test
set amount = (left(amount,len(amount)-1) +
(SELECT POS_SYMBOL FROM SIGN_CONVERSION WHERE CENT_VALUE =
right(amount,1)))

While it doesn't work, I think it gives you a good idea of what I'm after. The current code creates a cursor and then calls another subroutine that updates the passed field. I know there is a better way and I think I'm close. I tried writing a correlated subquery but I couldn't get it to work quite right.

suggestions?

Edited by - cat_jesus on 10/08/2001 11:54:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources