JIYIK CN >

Today, we will learn how to find a value in a PostgreSQL string and select it if it matches certain conditions.

Suppose you have a string carabcand want to see if it contains a value car. Therefore, you will try to use a function that tells you whether such a substring exists in the string.

PostgreSQL provides three methods: like, , similar toand Posixoperators. We will discuss these in detail.

In PostgreSQL, if a string contains a substring match, use likethe operatorSELECT

The expression will return True if the string contains the specific substring like; otherwise, False. First, we create a table and insert some values ​​into it:

create table strings(
    str TEXT PRIMARY KEY
);

insert into strings values ('abcd'), ('abgh'), ('carabc');

abcNow, let's select values ​​from this table .

select * from strings where str like 'abc';

When we run it, nothing happens. An empty table is displayed.

abcdWhy doesn't it return the sum from the table carabc? Take a look at the following statement and try to see what happens:

select * from strings where str like 'abcd';

Output:

So now you see that calling likewill often return the exact same string that we were trying to match.

To match it with a substring you need to add a ' to the substring you are trying to match %.

Here's a better example of how to do this:

select * from strings where str like '%abc%';

Output:

%xxTells the query that we need to find after some other value in the string comes after this x. If we wanted to find carabc, we would say %abc, and it would return carabcbecause caris appended abcbefore .

But if we want to go back abcd, we will use x%, since dis appended after x. Try it yourself and see the results to get a better understanding!

Since we want to check if a string contains which may be on the left, right or in the middle abc, we use which %abc%returns all such strings.

likeThe operator can also be written as ~~if, not likethen, use !~~.

Another expression is called Ilikehelps in matching strings without being case sensitive. This means if we use the following statement:

select * from strings where str Ilike '%Abc%';

Even though abcis not equal Abc, it returns the previous result because ais capitalized in the substring.

As with likethe operator, you can Ilikewrite as ~~*and not Ilikeas !~~*.

Suppose you want to run a query to check for records in a table that match a specific string. If you want abcdeto compare to the records in the table and return the matching rows, you can use:

select * from strings where 'abcd' like '%' || str || '%'

This will strappend the rows in the column %_str_%to the syntax of , and we can then compare each of the values ​​in there and see which ones match.

Output:

To match a substring with rogue characters like escape, , %or backslash , you can use,/、\

select * from strings where 'abcd' like '%/abc'

This will return nothing, since there is no way for a function to eliminate a value %. This brings us to our next point, using positionthe function.

In PostgreSQL, if a string contains a substring match, use position()the functionSELECT

position()The function is a better choice for checking if a substring exists within a string. This is defined under string operations in the PostgreSQL documentation.

It returns the index of the substring found in the main string. So if we are carabclooking for in car, it will return 1.

This way, we can see if any substring exists within the string by checking the value of the returned index. If it is greater than 0, then the substring exists; otherwise, it does not.

select * from strings where position(str in 'abcde') > 0

The above statement will return two values, aand abcd, because they both exist in abcde. You can manipulate them as needed.

Also, if you have any other characters in your substring, for example %, it will skip that character in the check and return the exact result, making it likemuch better than the expression.

select * from strings where position(str in '% abcde') > 0

Running the above will give the same results.

positionA possible alternative to the expression could be strpos, which is equally valid.

In PostgreSQL, if a string contains a substring match, use similar toa regular expression toSELECT

likeThe only difference between and similar tois that the latter is a standard SQL definition used in various other DMBSs.

select * from strings where position(str in 'abcde') > 0

This returns likethe same results as the expression.

To use alternative matching, you can use:

select * from strings where str similar to '%(abc|a)%'

This will return all strings that match abceither or a. When we run this query, we will return all the strings in the table because every string contains a a.

If you want to disable any metacharacters in the matched substring, you can use backslash \to disable what we tend to call rogue characters in strings.

In PostgreSQL, if a string contains a substring match, use substring()the functionSELECT

substring()Another way to manipulate the function is as follows:

select * from strings where str ~~ substring(str similar '%abc%' escape '#')

substring()In our case, returns strings that are similar to abcor contain abc. We then match the returned results with using ~~the operator ( short for ) , and if there is a match, we select the result from the table.likestr

This simple function even helps to split a string into its individual parts, as can be seen in the syntax provided:

substring(string similar pattern escape escape-character)

or

substring(string from a pattern for escape-character)

or

substring(string, pattern, escape-character)

escape-characterPrefers to split the string to be matched into different parts if it is contained at different points escape-character.

So if we run this statement:

select * from strings where str ~~ substring(str similar '#"abcd#"%' escape '#')

'#"abcd#"%'will be split into abcd, #enclosed by two characters. Hence, we can find the matching string as well abcd.

In PostgreSQL, if a string contains a substring match, use Posixthe regular operator toSELECT

The above is taken from the table in the PostgreSQL documentation describing Posixoperators that perform the same matching function.

You can use the following statement to check whether a string contains a substring:

select * from strings where str ~ 'abc'

This will return the values carabc​​and abcd.

You can even use it regexp_match(string, pattern [, flags])and return null if no match is found. If a match is found, it will return an array containing all the substrings that matched the pattern.

To understand this, look at the following query:

select regexp_match('abdfabc', 'abd')

Output:

Using another query,

select regexp_match('abdfabc', 'abf')

Output:

Now you will see how this expression finds a pattern and returns it. You can use this expression in a function, call the function to do selectthe operation, and then return all the matching strings.