Useful MySQL Queries for a Linguist Data Manager

Details

Category: Generalia

Published: 15 May 2021

 

Concatenate / combine multiple cell content with a separator and group them by another column .

SELECT distinct(groupByColumnName), GROUP_CONCAT(contentColumnToCombine SEPARATOR '; ') FROM `dbname`.`tableName` group by groupByColumnName;

 

Find fields/words with regex having one character in between, for example all words containing .aXe.
SELECT * FROM database.table where headword REGEXP '.*a.e.*'; and pron1 like '%end%' and charlen < 6;

Best method to backup and restore all DBs in MySQL. This ensures that the dbs are separate and does not have bulk issues.
Step 1. get a list of all the databases
Step 2: Create a batch file to dump all the DBs, one by one, using the following command:
    mysqldump -u user -pPassword dbname > dbname.sql
Step 3: Create a batch file to restore all the DBs, one by one, using the following command:
    mysql -u user -pPassword dbname < dbname.sql

 

Update a column with the character count of another column within the same table.

update dbName.tableName a set a.columnToUpdate=char_length(a.columnToCountCharacterOf) where a.columnToCountCharacterOf=a.columnToCountCharacterOf;

 

Load tab delimited data into a table:

Remove tabs from a given mysql field:

update dbName.TableName set ColumnName = replace(ColumnName,'\t','');

 

Enable Local Data load after logging into the MySQL prompt: SET GLOBAL local_infile=ON;

LOAD DATA LOCAL INFILE 'C:/director/importdata.txt' INTO TABLE dbName.tableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

select distinct(lang), (sum(textlength)) FROM dbName.tableName group by lang;


Count number of words in a field:
SELECT LENGTH(langtext) - LENGTH(REPLACE(langtext, ' ', ''))+1, id FROM dbName.tableName INTO OUTFILE "D:\\outdirector\\length.txt" limit 10;

Read more: Useful MySQL Queries for a Linguist Data Manager