how do I select numeric value by a pattern in SQL

Question

let's say I have a table called transaction and it looks like this

ID   Price
--- ----------- 
706         117.94       
707         151.60       
708         185.29       
719         117.94       
711         195.85

If Price column's type were varchar, then I could select entries by part of Price values.

For example, if I did:

 select * from transaction where Price like '%51%'

and I would get

707 151.60

However the Price column's type is numeric. How can I select entries by part of a numeric value?

What DBMS are you using? As @Alexander has pointed out, your query is perfectly valid in MySQL even if the column type were numeric. — PinnyM
– PinnyM, Commented Mar 20, 2014 at 19:21

Alexander · Accepted Answer · 2014-03-23 05:38:41Z

3

MySQL
SQL Server
Oracle
SQLite

Everyone of them supports LIKE on numbers.
It appears to be PostgreSQL you are using. With PostgreSQL we actually need to change datatype:

SELECT * FROM transaction1 WHERE CAST(Price AS text) LIKE '%51%';

PostgreSQL

edited Mar 23, 2014 at 5:38

answered Mar 20, 2014 at 19:28

Alexander

3,1992 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how do I select numeric value by a pattern in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related