create replication filter

Creates a replication filter object that defines conditions that RepAgent applies to a table in the Adaptive Server database.

The Adaptive Server RepAgent uses the replication filter at the primary database to determine if the data in a log record meets the criteria to send the data to the destination for any of the paths to which the filter is bound.After you bind a filter to a replication path, RepAgent only replicates the data that satisfies the filter conditions, through the path.

Syntax

create replication filter filter_name on table_name
as filter clause

Parameters

filter_name – the name of the filter object
table_name – the table name at the primary database that filter_name applies to

filter clause – the search conditions for the filter. . You can use:

Comparison operators – :

Operator	Meaning
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
!=	Not equal to
<>	Not equal to
!>	Not greater than
!<	Not less than

Ranges – use between and not between to select values in the column that are respectively within or outside a range of values you provide.
Lists – use in and not in to select values in the column that are respectively within or outside a list of values you provide.
Character matches – use like and not like to select values in the column that respectively match or do not match a value you provide. You can use wildcard characters in the value you want to match or not match with. See Adaptive Server Enterprise > Reference Manual: Building Blocks > Expressions, Identifiers, and Wildcard Characters > Pattern Matching with Wildcard Characters .
Unknown values – use is not null and is null to select columns in a row that respectively contain or do not contain data
Combinations of search conditions – use and and or to select values in columns that meet the combined conditions
Built-in expressions – use expressions available in the database such as date and time to select values in the column. You can only use expressions that always return the same value as long as you provide the same input. Other built-in expressions you can use include: cast:charindex, convert, dateadd, datediff, datename, datepart, isnull, substring, abs, acos, ascii, asin, atan, atn2, ceiling, char, cos, cot, datalength, octet_length, degrees, difference, radians, exp, floor, isnumeric, log, log10, lower, ltrim, pi, replicate, right, round, rtrim, sign, sin, space, sqrt, str, strtobin, stuff, tan, upper, power, patindex, reverse, char_length, character_length, hextoint, inttohex, daytonum, compare, day, left, len, month, str_replace, square, year, hextobigint, biginttohex, hash, hashbytes

Examples

Example 1 – Comparison operators – greater than ">":
Create a filter named advance_vs_revenue for the sales table to select only records where the value in the advance column multiplied by 2 is more than the value in the total_sales column multiplied by the value in the price column:
```
create replication filter advance_vs_filter on sales
as advance * 2 > total_sales * price
```
Example 2 – Ranges – between:
Create a filter named filter_in__sales for the sales table to select only records where the values in the total_sales column are between 4095 and 12000 :
```
create replication filter filter_in_sales on sales
as total_sales between 4095 and 12000
```
Example 3 – Ranges – not between:
Create a filter named filter_out_sales for the sales table to select only records where the values in the total_sales column are not between 4095 and 12000 :
```
create replication filter filter_out_sales on sales
as total_sales not between 4095 and 12000
```
Example 4 – Lists – in:
Create a filter named state_in for the sales table to select only records where the state in the state column is one of CA, IN, or MD:
```
create replication filter state_in on sales
as state in ("CA", "IN", "MD")
```
Example 5 – Lists – not in:
Create a filter named state_out for the sales table to select only records where the state in the state column is not one of CA, IN, or MD:
```
create replication filter state_out on sales
as state not in ("CA", "IN", "MD")
```
Example 6 – Character matches – like:
Create a filter named author_lastname for the books table to select only records with author last names in the au_lname column that meet the "[CK]ars[eo]n" seach condition where the last names must have "C" or "K" as the first character, followed by "a", "r", and "s" in that order, "e", or "o" as the next character, and "n" as the last character:
```
create replication author_lastname on books
as au_lname like "[CK]ars[eo]n"
```
Carson and Karsen meet the condition but Larson and Karsin do not.
Example 7 – Character matches – not like:
Create a filter named not_phone_num for the contacts table to select only records with telephone numbers in the phone column that do not contain the digits "415" at the beginning of the telephone number:
```
create replication not_phone_num on contacts
as phone not like "415%"
```
Example 8 – Unknown values – null:
Create a filter named advance_null for the sales table to select only records with null values in the advance column:
```
create replication advance_null on sales
as advance is null
```
Example 9 – Known values – not null:
Create a filter named advance_not_null for the sales table to select only records with values that are not null in the advance column:
```
create replication advance_not_null on sales
as advance is not null
```
Example 10 – Combinations of search conditions – <, or, between:
Create a filter named advance_vs_totalsales for the sales table to select only records where either the value in the advance column is less than 5000, or the value in the total_sales column is between 2000 and 2500:
```
create replication advance_vs_totalsales on sales
as advance < 5000 or total_sales between 2000 and 2500 
```
Example 11 – Built-in expressions – getdate():
Create a filter named older for the sales table to select only records where the value in the date column is older than the current date:
```
create replication older on sales
as date > getdate()
```
Example 12 – Boolean values – true:
Create a filter named all_rows for the sales table to select all records in the table:
```
create replication all_rows on sales
as true
```

Usage

You can create a replication filter only for a table in the primary database you are replicating from.
You must create the filter for a table in the database that contains the table.
Execute create replication filter on the primary Adaptive Server database where you want to apply the filters on the connections to the Replication Server.
Replication filters you create for a database must have a unique name within that database.
You must drop a replication filter before you create a new one of the same name.
You must enclose search condition strings in double quotations—" ".
You cannot combine create replication filter statements with other statements in a single batch.
Replication filters must not contain subqueries, order by, group by or having clauses.
Replication filters must not contain aggregate functions, user defined functions, functions that access system tables, or nonmaterialized columns.
You cannot create a replication filter on a column with the datatype: text, unitext, rawobject, image, or xtype (java classes).
To configure multiple replication paths, see the Replication Server Administration Guide Volume 2 > Performance Tuning > Multi-Path Replication.
Replication Server replicates create replication filter in warm standby applications.

Permissions

create replication filter requires “create object” permission.