Find and Replace script

[Trond Hoyem]

Hi

I am trying to create a script that can find a certain text inside the object name and replace it with another string. This is so I do not need to do this manually every time there is a large change.

For normal GA I would just use the update from ESF-file solution, but I often have a lot of virtual addresses as well, and they also must be updated.

I am able to find the adresses to update, and to generate the new name, but for some reason I am not able to update the name of the object. If anyone has an idea of why, please give me a hint.

The code I have entered is as follows;

Code:

finn = '360.001'
replace = '360.002'
kriterie = '08-0'



liste = db:getall('SELECT name FROM objects WHERE name LIKE "%'.. kriterie .. '%" AND name LIKE "%'.. finn .. '%"')

--
for _, name in ipairs(liste) do
  navn = name.name
  --log(navn)
  if string.find(navn, finn, 1, true) == nil then
    log('fikk nil')
  else
    log('fant strengen')
    newName = string.gsub(navn, finn, replace)
    log(navn, newName)
    ga = grp.find(navn)
    --db:update('objects', { name = newName }, { id = id })
    --log(ga)
    address = grp.create({
        address = ga.address,
        name = newName,
      })
  end
 
end
log('Ferdig')

script.disable(_SCRIPTNAME)

I am sorry for Norwegian names in variables, but that makes it more easy to prevent using a word that has a LUA-meaning....

[admin]

Use direct db queries (db:update), there's no point in grp.create here. Don't forget to add `id` to SELECT fields.
If you only need to match beginning of the name you can omit the first %. You can also use one LIKE statement instead of two.

[Trond Hoyem]

Use direct db queries (db:update), there's no point in grp.create here. Don't forget to add `id` to SELECT fields.
If you only need to match beginning of the name you can omit the first %. You can also use one LIKE statement instead of two.

I am not sure how to phrase the update in the SQL statement. How do I state what should be replaced with what?

[admin]

After gsub in your code:

Code:

db:update('objects', { name = newName }, { id = name.id })

[Trond Hoyem]

After gsub in your code:

Code:

db:update('objects', { name = newName }, { id = name.id })

Great, now this is working!

But then there is a nother problem. In many projects there has been the use of special Norwegian characters. If I want to find and replace that it doesn't seem to work.

Example, I have a name; string: 08-09 UTE persienner vest lysniv�
And I want to replace lysniv� with lysnivaa. This part is not really working, even though the log states that the string has been founs, but the replacement string is the exact same.

Is this maybe something that we are not able to fix?

[admin]

You can fix encoding of your ESF here: https://openrb.com/convert/

[Trond Hoyem]

You can fix encoding of your ESF here: https://openrb.com/convert/

Yea, but as I recall, that one does not work with large files. 
It is not a big thing, we are normally not using the special characters, but sometimes they are there.. :-)

For anyone that might need the same function, Here is the working code, a little cleaned up:

Code:

finn = 'Text to find'
replace = 'Text to add'
kriterie = 'Filter criteria'

liste = db:getall('SELECT name, id FROM objects WHERE name LIKE "%'.. kriterie .. '%"')

for _, name in ipairs(liste) do
  navn = name.name
  newName = string.gsub(navn, finn, replace)
  log(navn, newName)
  db:update('objects', { name = newName }, { id = name.id })
end
log('FINISHED')

script.disable(_SCRIPTNAME)