CODEWITHSUNDEEP
mysqlsqlmysql-workbench
user11188494
user11188494

Reputation: 1

SQL how to filter out values with two decimals

How do I filter out values for just one decimal place. For example, my column has values VP279.27 and VP 279.27.1 I only want the values for numbers that only have 1 decimal place like VP279.27

How would I do this in SQL? What does my where filter look like?

Upvotes: 0

Views: 1437

Answers (6)

m1nkeh
m1nkeh

Reputation: 1397

My take:

select *
from yourTable
where col regexp '^[^\.]*\.[^\.]*$';

does the job 👍

Demo -> http://sqlfiddle.com/#!9/dacd57/1

Upvotes: 0

FanoFN
FanoFN

Reputation: 7124

It's also possible with:

SELECT * FROM yourTable
WHERE col = SUBSTRING_INDEX(col,'.',2);

Or maybe, what you really want is not a condition in WHERE:

SELECT col, SUBSTRING_INDEX(col,'.',2) FROM yourTable;

Upvotes: 0

Wingman
Wingman

Reputation: 1

If you need to match Chinese words, then the 'char_length()' will be better than 'length()'.

SELECT
    * 
FROM
    `table_name` 
WHERE
    (char_length(`col_name`) - char_length(replace(`col_name`, '.', ''))) = 1;

Upvotes: 0

nayi224
nayi224

Reputation: 565

It will be easier to use replace

where length(col) - length(replace(col, '.', '')) = 1

Upvotes: 0

forpas
forpas

Reputation: 164174

For the sample data in your question you can use NOT LIKE:

SELECT *
FROM tablename
WHERE col NOT LIKE '%.%.%'

This will filter out any value that has at least 2 dots.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

You could use REGEXP here:

SELECT *
FROM yourTable
WHERE col NOT REGEXP '\\.[0-9]+\\.[0-9]+$';

Demo

The logic here is to filter off any record having a column value with 2 (or more) dot separated numbers. Just a single dot followed by an ending numbers is still allowed.

Upvotes: 1

Related Questions