0
For example, if I am going to do a database search of a table with the price of the products, I use in my condition Where: preco_product between 10 and 550 but and for me to do the same type of search in a column of type Character? Because when trying to do the search using the between it ends up returning an error. In the table below I would like to filter from the carlis_name column all records between for example 400 mAh and 1400 mAh.
1
It is possible, however string and number are read in different ways, the number is read respecting the values of each position, (units, tens, hundreds etc.), the string from left to right, therefore
400 é maior que 1400, já que lido da esquerda para direita, o 4 é maior que o 1.
I saw that your tags are mysql, postgresql and sql, I do not use Mysql, but in Postgres you can try to convert the string field into int as:
to_number(carlis_nome, '999999999') BETWEEN 400 AND 1400.
In Mysql I think you have to extract ' mAh' with:
CONVERT(SUBSTRING_INDEX(campo,' mAh',-4),UNSIGNED INTEGER) AS num
This one I can’t test.
1
I think that settles it for you
MYSQL
SELECT
campos
FROM
tabela
WHERE CAST(REPLACE(
LEFT(TRIM(`carlis_nome`), LOCATE(' ', TRIM(`carlis_nome `))),
'.','') AS SIGNED) BETWEEN 400 AND 1400;
POSTGRES
SELECT
campos
FROM
tabela
WHERE CAST(regexp_replace(carlis_nome , '^(\\d)\.(\\d+)\\s.*?', E'\\1\\2') AS INTEGER) BETWEEN 400 AND 1400;
Browser other questions tagged mysql sql postgresql
You are not signed in. Login or sign up in order to post.
You’d have to cut the part that’s string.
– rray
Show how you are doing the query and the table structure (it may be only the columns relevant to the query).
– Maniero
@Mustache I pick her up from another 2 or 3 tables. I really wanted to know how I can do to search using the between or if there is some other way to do this search or if for example to do some regular expression to select only the numbers in the column
– Gabriel Schmidt Cordeiro