CODEWITHSUNDEEP
postgresql
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3372

Finding a Specific Character in SQL Statement

Is there a way I can find a certain character using LIKE keyword?

For example, let's say I have the following values in COL1:

1.75
5.50
5.67

I want to write a SQL statement that only returns values from COL1 where the second character AFTER the decimal is a zero (regardless of what the rest of the number says.)

Upvotes: 1

Views: 92

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176214

Each value goes in this format: "#.##"

Then you could use wildcards % and _:

  • an underscore _ in pattern stands for (matches) any single character;
  • a percent sign % matches any sequence of zero or more characters.

Query:

SELECT * 
FROM tab 
WHERE col1 LIKE '%._0%';

SqlFiddleDemo

Output:

╔══════╗
║ col1 ║
╠══════╣
║ 5.50 ║
╚══════╝

If values are only numbers (with two places only) you could use simple modulo division:

SELECT *
FROM tab 
WHERE col1 % 0.1 = 0;

SqlFiddleDemo2

Upvotes: 1

Related Questions