COUNT ROWS that ONLY CONTAIN numbers-CodePudding

I need to count the number of rows in my table where the column name only contains numbers.

The name column is a text column and contains entries like; 'My Tour' 'My number 1 Walking route' and '1234'

I'm using this query:

SELECT name
FROM mytable
WHERE name LIKE '%[0-9]%'

Using the example above, I'm expecting to return the name 1234 only as it's it the only name that contains just numbers.

But nothing is returning?

CodePudding user response：

Postgres solution:

SELECT COUNT(*)
  FROM my_table
 WHERE name ~ '^[\d] $'
;

CodePudding user response：

SELECT count(*) as total_row FROM mytable WHERE name LIKE '%[0-9]%'

CodePudding user response：

try this select count(*) from mytable where name similar to '[\d] '

CodePudding user response：

For MySQL, MariaDB

SELECT COUNT(*) AS `total` FROM `table` WHERE `column` REGEXP '^[0-9] $'