Freeform SQL is an option when looking at a Translation Memory (TM). It allows the user to search the TM using a SQL query. The checkbox needs to be selected to view the
Available Fields and the area to
Enter Freeform SQL Search Query.
When using Freeform SQL the part of the SQL query being modified is the
WHERE clause.
More about the WHERE clause here.
The
Available Fields on the left can be double-clicked to move them to the Query.
To search for exact text, use the equal sign with the text to search in quotes.
[Source] = 'Test'
To search for text that contains a word and can have any text around it, use % in front of, or behind, the text to be searched. This symbol acts as a wildcard.
[Source] like '%Test%'
Multiple conditions can be added in the query. For example, users can search for results which match both the Source and the Target. Separate the fields to search with "and".
[Source] = 'test' and [Target] = 'test'
Freeform SQL queries have the following rules:
- Apart from the variable column names and date notation used, everything you type must be in raw SQL format. For example, you must type the character "
%" to denote wildcard and the comparison operator "like" to perform containment or substring search. - To search for single quote character, use double quote.
- To search for single backslash character, use double backslash.
- To search for the SQL wildcard characters "
%" and "_", escape them using a backslash. - Enclose dates in braces
{} using the date format specified in the strings.properties file. For example, MM/dd/yy hh:mm AM (or PM).
Note: Freeform SQL search executes a query using the SQL syntax supported by the database. The syntax differs between databases. Consult the SQL manual for your database for information on proper syntax.
Below are some sample queries for Freeform SQL mode:
- Show me all translation memory entries whose source content contain "
nuts bolts rotat"
[Source] like ‘%nuts bolts rotat%’
- Show me all translation memory entries whose source content contain "
nuts"
[Source] like ‘%nuts%’
- Show me all translation memory entries whose source content contain "
nuts" OR "bolts"
[Source] like ‘%nuts%’ or [Source] like ‘%bolts%’
- Show me all translation memory entries whose target content contain "
nuts" AND "bolts"
[Target] like ‘%nuts%’ and [Target] like ‘%bolts%’
- Show me all translation memory entries whose target content contain "
nuts*bolts*rotat" (Order of words is implied; not the same as "nuts" AND "bolts" AND "rotat".)
[Target] like ‘%nuts%bolts%rotat%’
- Show me all translation memory entries whose target content contain "
(nuts" OR "bolts)" AND "(rotat)".
([Target] like ‘%nuts%’ or [Target] like ‘%bolts%’) and [Target] like ‘%rotat%’
- For SQL Server databases only, search for text containing non-ASCII (Unicode) characters. You must insert an
N before a string containing Unicode characters.
[Source] like N'%search string%'
Miscellaneous Examples
[Source] like '[^A-Z]%oracle[^A-Z]%corporation'
([Source] like '%' or [Target] like '%') and [Created By] = 'Admin' and [Modified By] = 'Admin' and [SID] is null and [Source] is not null
([Source] like '%oracle%' or [Source] like '%ebay%' or [Source] like '%Mold Layout%') and ([Target] like '%oracle%' or [Target] like '%ebay%' or [Target] like '%Mold Layout%')
[Source] like '%'
-- search for wildcard characters % and _
[Source] like '%\%%' and [Source] like '%\_%'
-- search for 4 single '
[Source] like '%''''''''%'
-- search for single \
[Source] like '%\\%'
[Source] like '%' order by [Source]
[Source] like '%' order by [Source] desc
[Source] like '%' order by [Modified On]
[Source] like '%' order by [Modified On] asc
[Source] like '%' order by [Modified On] desc
[Source] like '%' order by [Source], [Target]
[Source] like '%' order by [Source], [Target], [Created By], [Modified By], [Created On], [Modified On]
[Source] like '%' order by [Source] desc, [Target] desc, [Created By], [Modified By], [Created On] desc, [Modified On] desc
[Source] like 'eBay : Crafts Seller Guide : Advanced Strategies'
[Source] = 'eBay : Crafts Seller Guide : Advanced Strategies'
[Source] like 'What works for one company may not work for another. ' or [Source] like 'eBay : Crafts Seller Guide : Advanced Strategies' or [Source] like 'Therefore, you should not use this as a formula for success. '
[Source] in ('What works for one company may not work for another. ', 'eBay : Crafts Seller Guide : Advanced Strategies', 'Therefore, you should not use this as a formula for success. ')
[Created By] = 'Admin' and ([Created On] between {01/22/04 11:15 AM} and {01/22/04 11:16 AM})
[Created By] = 'Admin' and [Modified On] > = {01/22/04 11:15 AM}
[Created By] = 'Admin' and [Modified On] < {01/22/04 11:15 AM}
[Created On] between {01/20/04 11:15 AM} and {01/22/04 11:16 AM} order by [Created On]
[Created On] between {01/20/04 11:15 AM} and {01/22/04 11:16 AM} order by [Source], [Target], [Created By], [Modified By], [Created On], [Modified On]
