Filtering with MS SQL

Filtering with MS SQL uses standard SQL WHERE clause syntax. MS SQL (Microsoft SQL Server) supports a rich set of operators, functions, and data types for flexible querying.

Filter Syntax Rules

• MS SQL filters are case-insensitive by default (depending on collation settings)
• Use single quotes for string/text values: 'text value'
• Field names with spaces or special characters must be enclosed in square brackets [Field Name]
• Multiple filter criteria can be combined using AND and OR operators
• Date and datetime values should be formatted as strings: 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS'

Examples: 

Field Name	Filter
CustomerID	CustomerID = 'CUST001'
Name	Name = 'Acme Corporation'
Order Status	[Order Status] = 'Completed'
Description	Description LIKE '%bicycle%'
ProductCode	ProductCode LIKE 'PROD-%'
Email	Email LIKE '%@example.com'
Comments	Comments IS NULL
ShipDate	ShipDate IS NOT NULL
Notes	Notes = ''
ItemNumber	ItemNumber != ''
Amount	Amount > 1000
ModifiedDate	ModifiedDate >= DATEADD(day, -7, GETDATE())
Status	Status IN ('Active', 'Pending', 'Processing')
CustomerID	CustomerID NOT IN ('', 'CUST999', 'TEST001')
Status	Status = 'Active' AND Amount > 500
Product Type	[Product Type] = 'Hardware' OR [Product Type] = 'Software'
OrderDate	OrderDate >= '2025-01-01'
OrderID	OrderID IN (SELECT OrderID FROM Orders WHERE OrderDate >= '2025-01-01' AND CustomerID NOT IN ('', 'CUST001', 'CUST002'))

Important Notes:

• Field Names with Spaces: Always use square brackets [Field Name] when the field name contains spaces or special characters.
• String Values: Always enclose string values in single quotes 'value'.
• Date Format: Use ISO format 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS' for dates.
• NULL vs Empty String: NULL and empty string ('') are different - use IS NULL for NULL checks, use = '' for empty strings.
• Performance: Use indexed columns in WHERE clauses for better performance.
• Wildcards: % matches any sequence of characters, _ matches a single character.
• Case Sensitivity: Depends on database collation settings - default is usually case-insensitive.

Troubleshooting Tips:

• If a filter isn't working as expected, verify the exact field name and data type.
• Test filters directly in SQL Server Management Studio (SSMS) before using in Rapidi.
• Use parentheses to control operator precedence in complex conditions
• When using LIKE, remember that % is a wildcard (not * like in NAV)