syntax for replace() - 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 2005 Forums

Transact-SQL (2005)

syntax for replace()

Author

Topic

shinoykurian
Starting Member

4 Posts

Posted - 2009-11-22 : 07:29:27

can some one help me with excat syntax for the below query, i am getting syntax in error replace function

DECLARE tables_cursor CURSOR
FOR
SELECT ACCOUNTNUMBER FROM ACCOUNTS WHERE ACCOUNTLEVEL=4
OPEN tables_cursor
DECLARE @accName varchar(20)
FETCH NEXT FROM tables_cursor INTO @accName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC replace (@accName,'-','')
EXEC ('UPDATE ACCOUNTS SET ACCOUNTNUMBER='+@accName)
FETCH NEXT FROM tables_cursor INTO @accName
END
CLOSE tables_cursor
DEALLOCATE tables_cursor

Best Regards
Shinoy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-22 : 07:55:49

I can see no need for a cursor.
Just do this:
Update Accounts
set Accountnumber=replace(Accountnumber,'-','')
where Accountlevel=4

No, you're never too old to Yak'n'Roll if you're too young to die.

shinoykurian
Starting Member

4 Posts

Posted - 2009-11-23 : 04:03:26

Update Accounts
set Accountnumber=replace(Accountnumber,'-','')
where Accountlevel=4

I have a problem again, there is FK constraints in 4 other tables connected to this filed, how can update this accountnumber field in all these table together.

Best Regards
Shinoy

Sachin.Nand

2937 Posts

Posted - 2009-11-23 : 04:27:13

Read more on DELETECASCADE and UPDATECASCADE in BOL.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources