CAT-Tools/DéjàVu X/SQL

SQL in DVX

DVX is based on Transact-SQL, Microsoft's and Sybase's interpretation of a structured language used for querying databases. By using SQL you can either select (view or export) or process (update or remove) database segments satisfying certain criteria. The former is called SQL Select; the latter is called SQL Execute. The different editions of DVX offer the following support for SQL:

DVX Editor - none.

DVX Standard - SQL Select (see the DVX Standard manual).

DVX Professional - SQL Select and SQL Execute (see the DVX Professional manual).

DVX Workgroup - SQL Select and SQL Execute (see the DVX Workgroup manual).

By using SQL you can for example remove all database segments that were made by user John between January 2, 2004 and January 10, 2004 13:07, provided said strings do not contain the word 'dog'. Or if certain database segments have been linked to a certain client A, you can change the name of client A into client B after client B decides to take over client A. The examples can be made as complex as you want.

Using SQL in DVX is a very advanced topic, about which a lot has been written. The above manuals are very good sources about the usage of SQL in DVX. Another very good source is A Tinkerer's Guide to SQL in DVX, by Michael Farrell.

Examples of SQL usage in DVX memory databases

Note that SQL Select statements are entered via SQL Select on the mid left of the screen, while SQL Execute statements are entered via Database > Execute SQL. The underlined parts need to be filled in by yourself.

Change usernick A into usernick B

UPDATE Sentences SET UserNick = 'newnick' WHERE UserNick = 'oldnick'

Change client A into client B

UPDATE Translations SET Client = 'newclient' WHERE Client LIKE '*partofoldclientsname*'

Update client names based on actual project names or part of them (and not PrjID)

UPDATE Translations SET Client='newclient' WHERE ID IN (SELECT ID FROM Translations WHERE PrjID in (SELECT Projects.Prjid FROM Projects WHERE ((Projects.PrjTitle) LIKE "*partOfProjectName*")))

Update client names based on datestamps

UPDATE Translations SET Client='newclient' WHERE ID IN (SELECT ID FROM Sentences WHERE DateStamp >= #mm/dd/yyyy hh:mm# AND DateStamp < #mm/dd/yyyy hh:mm#)

You have to specify a date range, as the datestamp also includes information about the time. You can leave out the time part (hh:mm) if needed.

Remove numbers and the following tab in the beginning of sentences

Also, it is possible to include functions in SQL statements that add even more flexibility. The statement below could be useful for records that have been added to the database with alignment, in which case some weird patterns may occur. For example, it is possible that some records start with a number, followed by a tab (which appears as a long space in the database).

UPDATE Sentences SET Sentence=RIGHT(Sentence,LEN(Sentence)-instr(Sentence,chr(9))) WHERE ID IN (SELECT ID from Sentences WHERE Sentence LIKE '[0-9]*'+chr(9)+'*')

The above instructions can be converted to SQL Select statements (just for viewing or exporting) by omitting everything up to and including the first instance of WHERE. It is highly recommended to first test the SQL by making selections, before actually executing said statements.

Examples of SQL usage in DVX projects

The following SQL queries are used for checking target segments in a project where English is the source language and Dutch is the target language. For other languages codes, please check the DVX manual or on-line Help.

Checking punctuation
Open the Row Selector, select SQL Statement, paste the query listed below, and select Apply.
target_1043 like '* .*' OR
target_1043 like '* ,*' OR
target_1043 like '* ;*' OR
target_1043 like '* :*' OR
target_1043 like '* [?]*' OR
target_1043 like '* [!]*'
This will select all segments where a ".", ",", ";", ":", "?", "!" is preceded by a space.

Checking spaces in front of closing brackets and after opening brackets
Open the Row Selector, select SQL Statement, paste the query listed below, and select Apply.
target_1043 like '* )*' OR
target_1043 like '* *' OR
target_1043 like '* >*' OR
target_1043 like '*( *' OR
target_1043 like '* *' OR
target_1043 like '*< *'
This will select all segments where a ")", "]", ">" is preceded by a space or "(", "[", "<" is followed by a space.

Source includes period while target does not
source like "*.*" and target_1043 not like "*.*"

Target includes opening bracket but no closing bracket
target_1043 like "*(*" and target_1043 not like "*)*"

Source includes opening bracket while target does not
source like "*(*" and target_1043 not like "*(*"

Source includes closing bracket while target does not
source like "*)*" and target_1043 not like "*)*"

Target segment is longer than source
LEN(target_1043) > len(source)

Target segment is longer than a specific value (useful for e.g. subtitles or software strings)
LEN(target_1043) > 38

Space on position 4 of target
InStr(1,target_1043," ",1)=4

Source starts with Upper case while target starts with lower case
(asc(left(source,1)) between 96 and 122) AND (asc(left(Target_1043,1)) between 65 and 90)
(One statement!)