trouble with update, stuff, replace functions

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 2005 Forums

Transact-SQL (2005)

trouble with update, stuff, replace functions

Author

Topic

wonky donkey
Starting Member

15 Posts

Posted - 2007-05-17 : 04:35:13

Hello all. First time poster, be gentle :-)

I'm having trouble working out why i cant combign certain functions within a query in SQL server 2005. I have a string, 9 characters long, some of them beginning with CX. Now, i know that any of the numbers that start with CX0 need to be changed to 200 and any number with CX(1-9) need to be replaced with 19.

I've found so many different ways of doing this, perhaps the best way being the STUFF function, which works fine, however my problem is i cant get it to include a WHERE clause, it seems ot ignore it.

Also, not only do i have to change this data i have to update the data in a table. Is it possible to combign a stuff function within an update function???

cheers for any help with this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 04:37:41

maybe you can post some sample data and how would you like to change it to ?

wonky donkey
Starting Member

15 Posts

Posted - 2007-05-17 : 04:43:00

hi there. This is my current code. It works, but, how do i update the original table after the STUFF function has completed? Simply trying ot add an UPDATE function at the top doesnt work.

SELECT STUFF (Casenote, 1,2, '20')
FROM RepdataOutturnMaster
WHERE RepdataOutturnMaster.Casenote LIKE 'CX0%';

cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 04:51:20

something like this ?


declare @tbl table
(
	col	varchar(9)
)

insert into @tbl
select	'CX0ABCDEF'	union all
select	'CX1ABCDEF'	union all
select	'CX2ABCDEF'	union all
select	'200ABCDEF'

update	t
set	col	= case 	substring(col, 3, 1)
			when '0' then 	stuff(col, 1, 2, '20')
			else		stuff(col, 1, 2, '19')
			end
from	@tbl t
where	col	like 'CX[0-9]%'

select	* from @tbl

wonky donkey
Starting Member

15 Posts

Posted - 2007-05-17 : 07:29:53

The case function works brilliantly, thanks very much. I thought it might be possible to use it but wasnt sure. Think i'm trying to condense too many arguments into one function and the case splits it up and make sit a lot more efficient.

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources