Handy Library Manager
 for Windows


Help, how to topics > Library items > Library Data: Optimization, Cleanup, Modification, and SQL Function Integration


Library Data: Optimization, Cleanup, Modification, and SQL Function Integration

" Many users access the OPAC on their phones, so I was trying to make records as clean and clutter-free as possible with concise and uniform lines. I wanted "pages" to show as "p." and "illustrations" as "ill.". Any assistance you provide would be welcomed and appreciated. "

Before making any changes to your data, please follow these steps:

  1. Read all instructions carefully.
  2. Analyze the text you want to replace.
  3. Ensure you create a backup of your data.

When analyzing the text, consider if it is part of a longer word. For example, the word "page" appears in "unpaged."

You can review the content of the field using the Advanced Search feature.

You have two options for applying global changes: you can use the REPLACE function or the Run SQL feature.


library data optimization cleanup modification advanced search

Before you execute the Replace function, it's advisable to perform an Advanced Search to see where the substring occurs.

Performing an Advanced Search before the replacement operation can reduce the time needed for the replacement.


library data optimization cleanup modification search replace

REPLACE

  1. Click on "Replace."
  2. Select the field you want to modify.
  3. Choose the replacement mode.
  4. Enter the substring you want to replace: illustrations
  5. Enter the string you want to replace it with ill.
  6. Start from the first record.
  7. Click "Replace".

The Replace function reviews each record and replaces "illustrations" with "ill." Depending on your computer's speed, this process may take one minute or longer.

Note: To ensure that only whole words are replaced and not parts of words, you can add a space before and/or after the text
(e.g., " illustrations").


RUN SQL

You can make global changes in SQL using the UPDATE and REPLACE functions.

The REPLACE() function substitutes a specified substring within a string with another substring. Typically, the SQL REPLACE() function modifies all occurrences of the specified substring within a given table. Text manipulation is crucial in SQL as it helps cleanse your library data for more efficient representation.


library data optimization cleanup modification run SQL with UPDATE and REPLACE

To replace the word "illustrations" with "ill.," use the following SQL statement in the Execute SQL box:

UPDATE LIBRARY SET PHYSICAL = REPLACE(PHYSICAL, 'illustrations', 'ill.');

LIBRARY: The name of the table being updated.
PHYSICAL: The name of the field being updated.
REPLACE: The SQL function used to perform the replacement.

To replace the word "pages" with "p.," use the following SQL statement in the Execute SQL box:

UPDATE LIBRARY SET PHYSICAL = REPLACE(PHYSICAL, 'pages', 'p.');

This will effectively update the specified words in the PHYSICAL field of the LIBRARY table.




Copyright © 2024 · All Rights Reserved · PrimaSoft PC