How to filter data in DBConvert tools?

Use data filtering to get the ones that interest you most during databases migration and synchronization. 

Filtering can be used for:

- Look at the results for a certain period of time.
- Find results by specific conditions.
- To view or analyze a subset of the data in a large dataset.

For example, select all needed fields from the database text fields, combine string data or something else related to the text. It is very easy to do using a filter with a query and all these actions will be performed in a couple of seconds. Even a superficial knowledge of SQL-functions will help you significantly speed up data management and operation in the database during transferring.

The manual showing how to apply filters in our GUI is here.

1. Let’s start from the standard query example which shows how to select all needed fields with data from the specific table. This syntax is used:

Select A,B,C

2. The second example is about how to return all values in a string in UpperCase. In this case the following statement is applied: 

Select UPPER

3. Next function replaces capital letters with LowerCase and returns the result:

Select LOWER

4. The function CONCAT returns a string created by combining of the arguments.  More than two arguments can be specified. If one of the arguments is NULL, the returned result also is NULL.

Select CONCAT

Additionally to function CONCAT the function CONCAT_WS is used. It combines strings like the CONCAT, but inserts the separators between the arguments. If the separator argument is NULL, the result also is NULL. The arguments of a string which are equal to NULL are skipped.

Select CONCAT_WS

5. When it is necessary to extend the string to a certain number of characters by repeating a symbol, the LPAD and RPAD functions are used. This also is a union of strings. The functions have the following syntax:

string LPAD(str string, len integer, padstr string)

string RPAD(str string, len integer, padstr string)

The LPAD function returns the string str, left-padded with the string padstr to a length of len characters.

The RPAD function does the same - returns the string argument right-padded with the specified string.

6. Next functions help to get a substring.
Firstly, let's consider functions LEFT and RIGHT, which are similar in their actions:

string LEFT(str string, len integer)

string RIGHT(str string, len integer)

Function LEFT returns the leftmost len characters (the first symbols) of the string str. If any argument is NULL, NULL is returned.

RIGHT returns the rightmost len characters (the last symbols) of the string str, or NULL if any argument is NULL.

7. Function SUBSTRING returns a substring from string str with the length of len characters from position pos. If the len parameter is not specified, then the entire substring is returned beginning from position pos.

string SUBSTRING(str string, pos integer, len integer)

 8. The function SUBSTRING_INDEX is used to return a substring from a string str before the specified number of occurrences of the delimiter delim which is in the count position.

The count parameter can be either positive or negative. If count is positive, the position of the delimiter will be counted from the left and the characters to the right of the delimiter will be deleted. If count is negative, then the position of the delimiter is counted from the right and the characters to the left of the delimiter are deleted.

 9. Next function TRIM immediately removes leading and trailing spaces of the string. Using the parameter remstr , it's possibly to specify characters or substrings that will be deleted from the beginning and from the end of the string. Use the control parameters BOTH, LEADING or TRAILING to indicate where the characters will be deleted:

string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)

     BOTH - removes the substring remstr from the beginning and from the end of the string

     LEADING - removes leading spaces of the string

     TRAILING - removes trailing spaces of the string

10. The function REPLACE is used to replace the specified characters in the string:

string REPLACE(str string, from_str string, to_str string)

It returns the string str with all occurrences of the string from_str replaced by the string to_str. 

The function is multibyte safe.

 11. To insert a substring into a string the function INSERT is applied.

string INSERT(str string, pos integer, len integer, newstr string)

It returns the string str which is a result of inserting the substring newstr in the string str beginning at position pos. The parameter len specifies how many characters will be removed from string str , starting at position pos.

The function is multibyte safe.

The most common SQL-functions were reviewed in this article and now you can easily apply them in our filters. All other you can find on any SQL-language related website. For example here