I need replace a part of a string in a whole field at the same time

Hello experts, I need to make a query for replacing a part of a string in a database, for example

Cuenta
--------
123456
125469
277456

I need to replace 456 for XXX (is a string)

¿how can I make this query in delphi?

Thanks.
RemisglezAsked:
Who is Participating?
 
mikelittlewoodConnect With a Mentor Commented:
Actually that was a bit over complicated
I wasnt thinking too straight :o)

How about

UPDATE <TABLE>
SET <STRINGFIELD> = SubString(<STRINGFIELD>, 1, 3) + 'XXX'
WHERE SubString(<STRINGFIELD>, 4, 3) = '456'
0
 
kretzschmarCommented:
for what database?
0
 
RemisglezAuthor Commented:
Hi kretzschmar, the database is Foxpro IV (.dbf)
0
 
mikelittlewoodCommented:
Ok Im assuming that you have an index on the table which is a number, if so this will do it

UPDATE <Table>
SET <STRINGFIELD> = SubString(<STRINGFIELD>,1,3) + 'XXX'
WHERE <INDEXFIELD> IN
(
SELECT <INDEXFIELD> FROM <Table>
WHERE SubString(<STRINGFIELD>,4,3) = '456'
)

just replace the obvious bits with your table etc
0
 
BlackTigerXCommented:
or
use YourTable
replace all YourField with substr(YourField, 1, at("456", YourField)-1)+"XXX"+substr(YourField, at("456", YourField)+3, 50) for at("456", YourField) > 0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.