# String search and full-text functions

Search functions test patterns, find regular-expression matches, compare similar text and construct full-text search values. LIKE/GLOB patterns, regular expressions, trigrams and tsquery expressions are different query languages.

Use literal search when the text is literal. For full-text matching, build a tsvector and a tsquery and combine them with the SQL `@@` operator. Each family below documents its syntax, configuration and NULL behavior.

## Example: Testing an identifier pattern

```sql
SELECT like('invoice-%', 'invoice-123'),
       regexp_like('invoice-123', '^invoice-[0-9]+$')
FORMAT csv;
```

```text
1,true
```

## like and glob

`like(pattern,text[,escape])` and `glob(pattern,text)` expose the pattern operators
as function calls. The pattern comes first. LIKE folds ASCII case and accepts
one optional escape character; GLOB is case-sensitive and uses shell-style
wildcards. NULL arguments propagate.

```sql
SELECT like('he%','hello'),glob('h*','hello') FORMAT csv;
```

```text
1,1
```

## array_to_tsvector

Create a vector from literal array lexemes.

### Syntax

```text
array_to_tsvector(lexemes)
```

### Arguments

One-dimensional native string Array. Reject NULL or empty members; deduplicate and sort without normalization. NULL input propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(array_to_tsvector(array('rat','cat','rat')) AS TEXT) FORMAT csv;
```

```text
"'cat' 'rat'"
```

## json_to_tsvector

Build a search vector from selected JSON components.

### Syntax

```text
json_to_tsvector([configuration,]document,filter)
```

### Arguments

Same filters/configurations as jsonb_to_tsvector; original object order and duplicate keys are retained. NULL propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(json_to_tsvector('simple','{"title":"fat cats"}','"string"') AS TEXT) FORMAT csv;
```

```text
"'cats':2 'fat':1"
```

## jsonb_to_tsvector

Build a search vector from selected JSONB components.

### Syntax

```text
jsonb_to_tsvector([configuration,]document,filter)
```

### Arguments

Configurations simple/english/russian, default english. Filter is a JSON string or string array: string,numeric,boolean,key,all. Canonical JSONB key order and one gap between selected components. Native search limits apply; NULL propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(jsonb_to_tsvector('simple','{"title":"fat cats"}','"string"') AS TEXT) FORMAT csv;
```

```text
"'cats':2 'fat':1"
```

## levenshtein

Compute Unicode edit distance.

### Syntax

```text
levenshtein(source,target); optional insertion/deletion/substitution costs
```

### Arguments

At most 255 characters per input. Optional nonnegative 32-bit insertion/deletion/substitution costs. SQL NULL propagates.

### Returned value

INTEGER edit distance.

### Example

```sql
SELECT levenshtein('GUMBO','GAMBOL') FORMAT csv;
```

```text
2
```

## levenshtein_less_equal

Compute Unicode edit distance up to a threshold.

### Syntax

```text
levenshtein_less_equal(source,target,max_distance); optional insertion/deletion/substitution costs
```

### Arguments

At most 255 characters per input. Optional nonnegative 32-bit insertion/deletion/substitution costs. Threshold is last; negative threshold requests the full distance. Above threshold any larger value is valid. SQL NULL propagates.

### Returned value

INTEGER edit distance or a value greater than the threshold.

### Example

```sql
SELECT levenshtein_less_equal('GUMBO','GAMBOL',2) FORMAT csv;
```

```text
2
```

## numnode

Count lexemes and operators in a query.

### Syntax

```text
numnode(query)
```

### Arguments

Native tsquery or raw query text. Empty query yields zero; NULL propagates.

### Returned value

INTEGER

### Example

```sql
SELECT numnode(to_tsquery('simple','cat & dog')) FORMAT csv;
```

```text
3
```

## phraseto_tsquery

Construct a phrase query retaining gaps from stop words.

### Syntax

```text
phraseto_tsquery([configuration,]text)
```

### Arguments

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

### Returned value

Native tsquery usable in tables and subqueries.

### Example

```sql
SELECT CAST(phraseto_tsquery('english','The fat rats') AS TEXT) FORMAT csv;
```

```text
"'fat' <-> 'rat'"
```

## plainto_tsquery

Construct an AND query from ordinary text.

### Syntax

```text
plainto_tsquery([configuration,]text)
```

### Arguments

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

### Returned value

Native tsquery usable in tables and subqueries.

### Example

```sql
SELECT CAST(plainto_tsquery('english','The fat rats') AS TEXT) FORMAT csv;
```

```text
"'fat' & 'rat'"
```

## querytree

Render the indexable positive part of a query.

### Syntax

```text
querytree(query)
```

### Arguments

Ignore negative restrictions. Empty query yields empty text; unrestricted alternatives yield T. This helper does not create an index. NULL propagates.

### Returned value

String

### Example

```sql
SELECT querytree(to_tsquery('simple','cat & !dog')) FORMAT csv;
```

```text
"'cat'"
```

## regexp_count

Count nonoverlapping Unicode regex matches starting at a one-based character position.

### Syntax

```text
regexp_count(text, pattern[, start[, flags]])
```

### Arguments

Start defaults to 1 and must be positive; flags follow existing regex rules; empty matches advance one UTF-8 character.

### Returned value

INTEGER count, zero without matches, or NULL for any NULL argument.

### Example

```sql
SELECT regexp_count('123456789012','[0-9]{3}',2) FORMAT csv;
```

```text
3
```

## regexp_instr

Return the one-based character boundary of a selected regex match or capture.

### Syntax

```text
regexp_instr(text,pattern[,start[,occurrence[,endoption[,flags[,subexpr]]]]])
```

### Arguments

Start/occurrence positive; endoption 0 selects the start, 1 the boundary after the end; subexpr nonnegative; existing bounded regex flags.

### Returned value

INTEGER position; 0 for no matching capture; NULL propagates.

### Example

```sql
SELECT regexp_instr('ABCDEF','c(.)(..)',1,1,0,'i') FORMAT csv;
```

```text
3
```

## regexp_like

Test whether a Unicode regex matches anywhere in the input.

### Syntax

```text
regexp_like(text, pattern[, flags])
```

### Arguments

Text, Unicode regular expression, optional supported flags; g is rejected.

### Returned value

Native Bool, or NULL for any NULL argument.

### Example

```sql
SELECT regexp_like('Hello World','world$','i') FORMAT csv;
```

```text
true
```

## regexp_match

Return the first regex match or its capturing groups.

### Syntax

```text
regexp_match(value, pattern[, flags])
```

### Arguments

Flags support i,c,s,n,m,p,w,x,t,q,e; active q and x modes are incompatible. Uses Unicode regex syntax; look-around, pattern backreferences and POSIX longest-match behavior are not supported.

### Returned value

Native array of nullable TEXT capturing groups, or the whole match when there are no groups; NULL for no match or NULL input.

### Example

```sql
SELECT array_element(regexp_match('abc123','([a-z]+)([0-9]+)'),2) FORMAT csv;
```

```text
"123"
```

## regexp_matches

Return regex match groups as native arrays in separate FROM rows.

### Syntax

```text
FROM regexp_matches(text,pattern[,flags])
```

### Arguments

Existing regex syntax and flags; g emits all matches, otherwise the first; NULL input gives no rows.

### Returned value

One regexp_matches column containing Array(Nullable(String)); no-match input gives no rows.

### Example

```sql
SELECT array_to_string(regexp_matches,',') FROM regexp_matches('a1 b2','([a-z])([0-9])','g') FORMAT csv;
```

```text
"a,1"
"b,2"
```

## regexp_substr

Return the requested regex occurrence or capture, preserving anchors against the original string.

### Syntax

```text
regexp_substr(text, pattern[, start[, occurrence[, flags[, subexpr]]]])
```

### Arguments

Positive one-based start/occurrence default to 1; nonnegative subexpr defaults to whole match 0.

### Returned value

TEXT, or NULL for no match, unmatched/missing capture, or any NULL argument.

### Example

```sql
SELECT regexp_substr('ABCDEFGHI','(c..)(...)',1,1,'i',2) FORMAT csv;
```

```text
"FGH"
```

## setweight

Assign positional weights to all or selected lexemes.

### Syntax

```text
setweight(vector,weight[,lexemes])
```

### Arguments

Weight A/B/C/D; optional native string Array selects literal lexemes. NULL propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(setweight(to_tsvector('simple','cat dog'),'A') AS TEXT) FORMAT csv;
```

```text
"'cat':1A 'dog':2A"
```

## show_trgm

List sorted unique compact trigrams.

### Syntax

```text
show_trgm(text)
```

### Arguments

Same Unicode word/padding/lowercase rules as similarity. Multibyte compact keys use PostgreSQL legacy CRC and little-endian byte extraction. NUL errors. SQL NULL propagates.

### Returned value

Array(String): printable ASCII keys or hexadecimal compact keys.

### Example

```sql
SELECT array_to_string(show_trgm('cat'),'|') FORMAT csv;
```

```text
"  c| ca|at |cat"
```

## similarity

Compare unique trigram sets.

### Syntax

```text
similarity(pattern,text)
```

### Arguments

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

### Returned value

Native Float32 score in \[0,1]; zero for empty trigram sets.

### Example

```sql
SELECT similarity('word','two words') FORMAT csv;
```

```text
0.36363637
```

## strict_word_similarity

Maximize similarity over extents aligned to whole-word boundaries.

### Syntax

```text
strict_word_similarity(pattern,text)
```

### Arguments

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

### Returned value

Native Float32 score in \[0,1]; zero for empty trigram sets.

### Example

```sql
SELECT strict_word_similarity('word','two words') FORMAT csv;
```

```text
0.5714286
```

## strip

Remove positions and weights from a vector.

### Syntax

```text
strip(vector)
```

### Arguments

Native tsvector or vector input text. Retain lexemes; NULL propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(strip(to_tsvector('simple','cat dog')) AS TEXT) FORMAT csv;
```

```text
"'cat' 'dog'"
```

## to_tsquery

Parse Boolean, prefix, weight and phrase query syntax.

### Syntax

```text
to_tsquery([configuration,]text)
```

### Arguments

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

### Returned value

Native tsquery usable in tables and subqueries.

### Example

```sql
SELECT CAST(to_tsquery('english','fat & rats') AS TEXT) FORMAT csv;
```

```text
"'fat' & 'rat'"
```

## to_tsvector

Normalize document words and retain lexeme positions.

### Syntax

```text
to_tsvector([configuration,]text)
```

### Arguments

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

### Returned value

Native tsvector usable in tables and subqueries.

### Example

```sql
SELECT CAST(to_tsvector('english','The fat rats') AS TEXT) FORMAT csv;
```

```text
"'fat':2 'rat':3"
```

## ts_delete

Remove literal lexemes from a vector.

### Syntax

```text
ts_delete(vector,lexeme_or_array)
```

### Arguments

One string or native string Array; ignore absent and NULL array members. NULL input propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(ts_delete(to_tsvector('simple','cat dog'),'dog') AS TEXT) FORMAT csv;
```

```text
"'cat':1"
```

## ts_filter

Keep selected positional weights.

### Syntax

```text
ts_filter(vector,weights)
```

### Arguments

Native string Array of A/B/C/D weights. Reject invalid or NULL members. NULL input propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(ts_filter(setweight(to_tsvector('simple','cat'),'A'),array('A')) AS TEXT) FORMAT csv;
```

```text
"'cat':1A"
```

## ts_headline

Highlight query lexemes in bounded document fragments.

### Syntax

```text
ts_headline([configuration,]document,query[,options])
```

### Arguments

Text documents; configurations simple/english/russian. Options StartSel/StopSel/MinWords/MaxWords/MaxFragments/FragmentDelimiter/HighlightAll. Native deterministic word-window selection can differ from PostgreSQL. Default HTML-like tags are removed; HighlightAll retains source markup. Returned text is not HTML-escaped. SQL NULL propagates.

### Returned value

TEXT with selected lexemes marked.

### Example

```sql
SELECT ts_headline('english','raining cats and dogs',to_tsquery('english','cat')) FORMAT csv;
```

```text
"raining <b>cats</b> and dogs"
```

## ts_rank

Rank matching lexemes by frequency/proximity.

### Syntax

```text
ts_rank([weights,]vector,query[,normalization])
```

### Arguments

Native/search-text tsvector and tsquery. Optional Array of at least four non-NULL weights, ordered D/C/B/A; defaults 0.1/0.2/0.4/1. Negative weights select defaults, values above one error. Normalization mask 0..63. Stripped vector entries contribute to frequency but not cover density. SQL NULL propagates.

### Returned value

Native Float32 relevance score.

### Example

```sql
SELECT ts_rank(to_tsvector('simple','cat'),to_tsquery('simple','cat')) FORMAT csv;
```

```text
0.06079271
```

## ts_rank_cd

Rank matching lexemes by minimal cover density.

### Syntax

```text
ts_rank_cd([weights,]vector,query[,normalization])
```

### Arguments

Native/search-text tsvector and tsquery. Optional Array of at least four non-NULL weights, ordered D/C/B/A; defaults 0.1/0.2/0.4/1. Negative weights select defaults, values above one error. Normalization mask 0..63. Stripped vector entries contribute to frequency but not cover density. SQL NULL propagates.

### Returned value

Native Float32 relevance score.

### Example

```sql
SELECT ts_rank_cd(to_tsvector('simple','cat'),to_tsquery('simple','cat')) FORMAT csv;
```

```text
0.1
```

## tsquery_phrase

Join two queries at an exact positional distance.

### Syntax

```text
tsquery_phrase(left,right[,distance])
```

### Arguments

Native tsquery or raw query text; distance 0..16384, default 1. Native depth/term limits apply. NULL propagates.

### Returned value

tsquery

### Example

```sql
SELECT CAST(tsquery_phrase(to_tsquery('simple','cat'),to_tsquery('simple','dog'),2) AS TEXT) FORMAT csv;
```

```text
"'cat' <2> 'dog'"
```

## tsvector_to_array

Extract sorted unique search lexemes.

### Syntax

```text
tsvector_to_array(vector)
```

### Arguments

Native tsvector or vector input text. Positions and weights are omitted. SQL NULL propagates.

### Returned value

Array(String) lexemes.

### Example

```sql
SELECT array_to_string(tsvector_to_array(to_tsvector('simple','rat cat rat')),',') FORMAT csv;
```

```text
"cat,rat"
```

## websearch_to_tsquery

Parse quoted phrases, OR and leading-minus query terms.

### Syntax

```text
websearch_to_tsquery([configuration,]text)
```

### Arguments

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

### Returned value

Native tsquery usable in tables and subqueries.

### Example

```sql
SELECT CAST(websearch_to_tsquery('english','The fat rats') AS TEXT) FORMAT csv;
```

```text
"'fat' & 'rat'"
```

## word_similarity

Maximize similarity to a contiguous extent of text trigrams.

### Syntax

```text
word_similarity(pattern,text)
```

### Arguments

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

### Returned value

Native Float32 score in \[0,1]; zero for empty trigram sets.

### Example

```sql
SELECT word_similarity('word','two words') FORMAT csv;
```

```text
0.8
```