Menu
I have character varying entries in a table where some (not all) values contain percentages, e.g., '12%', '97%', etc. I want to find all the values that contain percentages. In other words, I want to find all values that end with a percent sign ('%').
521
We have several options if we want to display numbers with a percentage sign in PostgreSQL. We can use the TO_CHAR() function to format the number along with the percentage sign. Or we can simply concatenate the number with the percentage sign, either with the CONCAT() function or with the concatenation operator.
The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. The percent sign represents zero, one, or multiple numbers or characters.
SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%\ /%'; Sometimes these ticks are very useful for searching special characters in a database.
The SQL LIKE Operator The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
In PostgreSQL, a literal is the same as a constant. We'll cover several types of literals - string literals, number literals, date and time literals and boolean literals. String literals are always surrounded by single quotes ('). For example: Number literals can be either positive or negative numbers that are exact or floating point values.
Postgres, like the query, are basically used to match the text values from the pattern we have used in the query example; we can match the text values using the wildcards operator. If the search expression matches the given condition, which we have used with like query, then like query will return the true values, the true value is considered one.
The percent sign (%) is used to represent zero, one, or many characters or numbers. The underscore wildcard (_) is used to represent one character or number. These symbols can also be combined.
If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character.
You can try like this:
SELECT * FROM my_table WHERE my_column LIKE '%\%%' ESCAPE '\'; Format
<like predicate> ::= <match value> [ NOT ] LIKE <pattern> [ ESCAPE <escape character> ] <match value> ::= <character value expression> <pattern> ::= <character value expression> <escape character> ::= <character value expression> You have to escape the literal % sign. By default the escape character is the backslash:
SELECT * FROM my_table WHERE my_column LIKE '%\%'; In this case the first % sign matches any starting sequence in my_column. The remaining \% are interpreted as a literal % character. The combination is therefore: match anything that ends in a % character.
SQLFiddle
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
