No Replace All Type Command - 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

General SQL Server Forums

New to SQL Server Programming

No Replace All Type Command

Author

Topic

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-12-27 : 19:25:20

As I am reading more and more on SQL and coming
from a Foxpro logic. I take it their is no real
"Replace All" type command in SQL and it appears
I would want to use an "Update".

I was hoping one of the SQL Masters might be
able to share some background on this or
perhaps a link on transitioning from Foxpro to
SQL with regards to the Replace function.

I am happy to read on my own. Its just information
seems to be limited and a little confusing..

Peace.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-27 : 20:16:49

I'm certainly no SQL Master, and don't know FoxPro at all. There is a REPLACE function in SQL.
DECLARE @var varchar(5)
SET @var = 'ababab'
SELECT REPLACE(@var,'a','c')
gives 'cbcbcb'
To change an entire column of 'ababab' with 'cbcbcb'
UPDATE yourTable
SET yourColumn = REPLACE(yourColumn,'a','c')
Books On Line is a great place to learn about SQL, and how you would do things in SQL that you did on FoxPro.

Jim

Dan OHaver
Starting Member

1 Post

Posted - 2008-12-27 : 23:12:58

Although I am not a SQL Master, I have transitioned a few database projects from Foxpro to SQL Server. I do not know of a book that specifically deals with this topic.

You are correct, T-SQL UPDATE replaces the REPLACE statement in Foxpro:

For example, to increase a retail price by 20% for all items:

FOXPRO
Replace ALL Price with Price * 1.20 in Products

SQL
UPDATE Products
SET Price = Price * 1.20

Please note, you can use nearly all basic T-SQL language in Foxpro; the UPDATE example above works in Fox as well (you'll need to put a ; in the line break after "Products").

The best resource may be to read a good book on beginning T-SQL and just plow through it.

Delete, like the UPDATE/Replace is an area you’ll need to focus on as well as any type of Foxpro SCAN … ENDSCAN areas in your FOX code.

You might find what you need right in Foxpro help, look up DELETE-SQL, UPDATE-SQL, SELECT-SQL in Foxpro’s help system.

Any SCAN…ENDSCAN code could be difficult to convert, not that it can’t be done with Cursors in SQL, but this is typically frowned upon in a SQL environment. Try to replace the looping functionality with a single UPDATE.

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-12-28 : 01:45:34

Thank you for the tips..It really helps.
I did get 2 begineer books for T-Sql and
have be working them..The example given also
gave form so I can work and learn from that.
The trouble starts when I go back to foxpro
command I know by heart and expect it to work..
I am sure you all understand, I have a learning
curve but I am getting there..
Thanx again..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources