Full-Text Search in Babelfish: What Works, What Doesn't, and Workarounds

SQL Server applications commonly rely on text search, from simple LIKE patterns to full-text predicates like CONTAINS and FREETEXT. If you are migrating to Babelfish, you need to know which of these work out of the box and which require changes.

This post documents every text search method we tested against Babelfish 4.8.0 on PostgreSQL 16.11, running as a FoundryDB managed service. Every query was executed and every result verified.

Test Setup

• Babelfish 4.8.0 on PostgreSQL 16.11
• Connected via sqlcmd on port 1433
• Fresh instance, no prior schema

We will use a simple articles table throughout:

CREATE TABLE articles (
    id         INT IDENTITY(1,1) PRIMARY KEY,
    title      NVARCHAR(200) NOT NULL,
    body       NVARCHAR(MAX) NOT NULL,
    category   NVARCHAR(50)  NOT NULL,
    created_at DATETIME2     DEFAULT GETDATE()
);

INSERT INTO articles (title, body, category) VALUES
('Getting Started with PostgreSQL', 'PostgreSQL is a powerful open-source relational database. It supports advanced features like full-text search, JSON, and window functions.', 'database'),
('Introduction to Babelfish', 'Babelfish for PostgreSQL lets you run SQL Server workloads on PostgreSQL. It supports T-SQL, TDS protocol, and many SQL Server data types.', 'database'),
('Building REST APIs with Node.js', 'Node.js is a popular runtime for building web APIs. Express and Fastify are common frameworks for handling HTTP requests.', 'webdev'),
('Search Engine Fundamentals', 'Full-text search engines use inverted indexes to find documents quickly. Tokenization, stemming, and ranking are core concepts.', 'search'),
('Cloud Database Best Practices', 'Managed databases reduce operational overhead. Automated backups, monitoring, and scaling are key benefits of cloud database platforms.', 'database');

LIKE: Fully Supported

All standard LIKE patterns work correctly.

Trailing Wildcard

SELECT id, title FROM articles WHERE title LIKE 'Getting%';

id  title
--  --------------------------------
1   Getting Started with PostgreSQL

Leading Wildcard

SELECT id, title FROM articles WHERE title LIKE '%PostgreSQL';

id  title
--  --------------------------------
1   Getting Started with PostgreSQL

Both Wildcards

SELECT id, title FROM articles WHERE body LIKE '%full-text search%';

id  title
--  --------------------------------
1   Getting Started with PostgreSQL
4   Search Engine Fundamentals

NOT LIKE

SELECT id, title FROM articles WHERE category NOT LIKE '%web%';

id  title
--  ----------------------------------------
1   Getting Started with PostgreSQL
2   Introduction to Babelfish
4   Search Engine Fundamentals
5   Cloud Database Best Practices

All four LIKE variants work identically to SQL Server. No issues.

PATINDEX: Fully Supported

PATINDEX returns the starting position of a pattern within a string. It supports the same wildcard characters as LIKE.

SELECT
    id,
    title,
    PATINDEX('%database%', body) AS match_position
FROM articles
WHERE PATINDEX('%database%', body) > 0;

id  title                              match_position
--  ---------------------------------  --------------
1   Getting Started with PostgreSQL    46
2   Introduction to Babelfish          60
5   Cloud Database Best Practices      72

PATINDEX is useful when you need to know where in the text a match occurs, not just whether it exists.

CHARINDEX: Fully Supported

CHARINDEX returns the position of a substring within a string. Unlike PATINDEX, it does not support wildcards, but it does accept an optional start position.

SELECT
    id,
    title,
    CHARINDEX('search', body) AS first_match
FROM articles
WHERE CHARINDEX('search', body) > 0;

id  title                          first_match
--  -----------------------------  -----------
1   Getting Started with PostgreSQL  75
4   Search Engine Fundamentals       11

CHARINDEX is case-insensitive by default in Babelfish, matching SQL Server behaviour for the default collation.

CONTAINS: Not Supported

SQL Server's full-text CONTAINS predicate is not available in Babelfish 4.8.0.

SELECT id, title FROM articles WHERE CONTAINS(body, 'database');

Msg 33557097, Level 16, State 1, Server managed-babelfish, Line 1
'CONTAINS/FREETEXT predicate' is not currently supported in Babelfish.
please use babelfishpg_tsql.escape_hatch_fulltext to ignore

The query fails immediately. Babelfish does not implement full-text indexes or the CONTAINS predicate.

FREETEXT: Not Supported

FREETEXT has the same limitation:

SELECT id, title FROM articles WHERE FREETEXT(body, 'database management');

Msg 33557097, Level 16, State 1, Server managed-babelfish, Line 1
'CONTAINS/FREETEXT predicate' is not currently supported in Babelfish.
please use babelfishpg_tsql.escape_hatch_fulltext to ignore

Same error. Neither CONTAINS nor FREETEXT will work regardless of configuration.

The escape_hatch_fulltext Setting

The error message suggests using babelfishpg_tsql.escape_hatch_fulltext. This setting controls how Babelfish handles unsupported full-text syntax:

-- Suppress the error (default is 'strict')
EXEC sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_fulltext', 'ignore';

Setting this to 'ignore' will suppress the error, but it does not enable full-text search. Queries using CONTAINS or FREETEXT will silently return no results or behave unpredictably. This setting exists to let applications start up without crashing on unsupported DDL (like CREATE FULLTEXT INDEX), not to provide actual functionality.

warning

Do not rely on the escape_hatch_fulltext setting for production search queries. It suppresses errors but does not implement the feature.

Workaround 1: Rewrite Queries with LIKE and PATINDEX

For simple keyword matching, LIKE and PATINDEX cover most use cases. If your SQL Server application uses CONTAINS for basic substring searches, you can often replace it directly:

-- SQL Server original
SELECT id, title FROM articles WHERE CONTAINS(body, 'database');

-- Babelfish replacement
SELECT id, title FROM articles WHERE body LIKE '%database%';

For multiple keywords, combine LIKE clauses:

-- SQL Server: CONTAINS(body, 'database AND search')
-- Babelfish replacement:
SELECT id, title FROM articles
WHERE body LIKE '%database%'
  AND body LIKE '%search%';

This approach works well for small to medium tables. For large datasets, be aware that LIKE with a leading wildcard triggers a full table scan on every query.

Workaround 2: PostgreSQL Native Full-Text Search

Every Babelfish instance on FoundryDB is a PostgreSQL database underneath. You can connect via the PostgreSQL port (5432) and use PostgreSQL's native full-text search, which is more capable than SQL Server's full-text search in many respects.

Connect to the PostgreSQL port using psql or any PostgreSQL driver:

-- Create a tsvector column and GIN index
ALTER TABLE articles ADD COLUMN body_tsv tsvector;

UPDATE articles SET body_tsv = to_tsvector('english', body);

CREATE INDEX idx_articles_body_tsv ON articles USING GIN (body_tsv);

-- Full-text search with ranking
SELECT id, title, ts_rank(body_tsv, query) AS rank
FROM articles, to_tsquery('english', 'database & search') AS query
WHERE body_tsv @@ query
ORDER BY rank DESC;

PostgreSQL's full-text search supports stemming, ranking, phrase matching, and prefix queries. For applications that need more than LIKE can provide, this is the recommended path.

You can keep using the TDS port (1433) for your application's T-SQL queries and route only the search-heavy queries through the PostgreSQL port.

Summary

Method	Supported	Notes
LIKE (all variants)	Yes	Works identically to SQL Server
NOT LIKE	Yes	Works identically to SQL Server
PATINDEX	Yes	Returns match position, supports wildcards
CHARINDEX	Yes	Returns substring position, no wildcards
CONTAINS	No	Error; escape_hatch can suppress but not enable
FREETEXT	No	Error; same limitation as CONTAINS
CREATE FULLTEXT INDEX	No	Not supported

For most migrations, replacing CONTAINS with LIKE is sufficient. For advanced search requirements, PostgreSQL's native full-text search via port 5432 is a powerful alternative that is already available on every FoundryDB Babelfish instance.

More in This Series

• Provisioning Babelfish and Connecting via TDS
• T-SQL Compatibility on Babelfish 4.8.0
• Migrating from SQL Server to Babelfish
• Babelfish Performance and Indexing
• Backups and Point-in-Time Recovery
• Connecting from .NET, Java, Python, and Node.js

Try FoundryDB for free at foundrydb.com and have a Babelfish instance running in under five minutes.