Find and Replace

Search and replace text patterns across records in any text-based field with preview and confirmation

This script provides a powerful find and replace functionality for text fields in your table. It allows you to search for specific text patterns and replace them with new values across multiple records, with the ability to preview matches before making changes.

const config = input.config({
  title: 'Find and replace',
  description: `This script will find and replace all text matches for a text-based field you pick.
    You will be able to see all matches before replacing them.`,
  items: [input.config.table('table', { label: 'Table' }), input.config.field('field', { parentTable: 'table', label: 'Field' })],
})

const { table, field } = config
output.text(`Finding and replacing in the ${field.name} field of ${table.name}.`)

const findText = await input.textAsync('Enter text to find:')
const replaceText = await input.textAsync('Enter to replace matches with:')

// Load all of the records in the table
let result = await table.selectRecordsAsync()

// Fetch all records using a while loop
while (result.hasMoreRecords) {
  await result.loadMoreRecords()
}

// Find every record we need to update
const replacements = []
for (const record of result.records) {
  const originalValue = record.getCellValue(field)

  // Skip non-string records
  if (typeof originalValue !== 'string') {
    continue
  }

  // Skip records which don't have the value set, so the value is null
  if (!originalValue) {
    continue
  }

  const newValue = originalValue.replace(findText, replaceText)

  if (originalValue !== newValue) {
    replacements.push({
      record,
      before: originalValue,
      after: newValue,
    })
  }
}
if (!replacements.length) {
  output.text('No replacements found')
} else {
  output.markdown('## Replacements')
  output.table(replacements)

  const shouldReplace = await input.buttonsAsync('Are you sure you want to save these changes?', [
    { label: 'Save', variant: 'danger' },
    { label: 'Cancel' },
  ])

  if (shouldReplace === 'Save') {
    // Update the records
    let updates = replacements.map((replacement) => ({
      id: replacement.record.id,
      fields: {
        [field.id]: replacement.after,
      },
    }))

    // Only up to 10 updates are allowed at one time, so do it in batches
    while (updates.length > 0) {
      await table.updateRecordsAsync(updates.slice(0, 10))
      updates = updates.slice(10)
    }
  }
}

Use Cases

• Data Cleanup : Fix typos, standardize formatting, or correct inconsistent data
• URL Updates : Update domain names or path structures across multiple records
• Content Migration : Replace old terminology with new branding or naming conventions
• Bulk Corrections : Fix systematic errors across large datasets

How it Works

1. Select Field : Choose any text-based field (Single Line Text, Long Text, Email, URL, etc.)
2. Enter Search Pattern : Specify the text you want to find
3. Enter Replacement : Define what the found text should be replaced with
4. Preview Matches : Review all matches before applying changes
5. Confirm Changes : Apply replacements to all matching records

Requirements

• A text-based field containing the data you want to modify
• Find pattern : The text string you want to search for
• Replace pattern : The text string you want to use as replacement

Supported Field Types

• Single Line Text
• Long Text
• Email
• URL
• Phone Number
• Any other text-based field

Example

Scenario : Update company domain across all email addresses

Before:

john@oldcompany.com
jane@oldcompany.com
admin@oldcompany.com

Find : oldcompany.com Replace : newcompany.com

After:

john@newcompany.com
jane@newcompany.com
admin@newcompany.com

Features

• Safe Preview : See all matches before making any changes
• Exact Matching : Finds only the exact text pattern specified
• Batch Processing : Updates all matching records in one operation
• Case Sensitive : Matches respect letter casing
• Non-Destructive : Only replaces the specified pattern, leaving other text unchanged

Best Practices

• Always use the preview feature to verify matches before applying changes
• Test with a small dataset first when working with large tables
• Consider case sensitivity when defining your search pattern
• Make sure your replacement text won't create unintended formatting issues

Related pages

• API Reference Manual Examples : Browse categorized examples from the NocoDB API reference documentation to quickly access common scripting tasks and operations.
• Convert Attachments to URLs : Convert attachment fields into comma-separated URL lists for integration with external services
• Randomize Values : Generate random data for empty fields across multiple data types with customizable ranges and constraints
• Validate Emails : Identify and list all invalid email addresses in email fields for data quality assurance
• Get Select Options : Extract and display all available options from Single Select or Multiple Select fields in various formats
• Save View Ordering to Field : Preserve manual record ordering from any view by saving it as sequential numbers in a numeric field
• Link Records by Field : Link records between two tables using matching field values, with pagination and summary reporting.
• Unique Field Values Summary : View unique values and their frequencies in any field, with summary and table output