How can I query for a substring with value> = 60000
I have a dataset with data formatted like this:
Date | exec_time ------------+--------- Today | 99999 ms Yesterday | 1 ms Tomorrow | 50000 ms Another Day | None Recorded Last Day | ms
I need to write a query to get all the values exec_time
that>= 60000
The way I tried to write it is as follows:
select exec_time
from myTable
where exec_time not like '%N%'
and cast(split_part(exec_time,' ', 1) as int) >= 60000
order by len(exec_time) desc, exec_time desc
limit 10
However, when I run this I get this error:
ERROR: Invalid digit, Value '2', Pos 0, Type: Integer Detail: ----------------------------------------------- error: Invalid digit, Value '2', Pos 0, Type: Integer code: 1207 context: query: 2780081 location: :0 process: query0_61 [pid=0] -----------------------------------------------
Any ideas how I can get around this?
source to share
Error: Conditions are WHERE
not met in any given order.
Use an operator CASE
to avoid an exception.
SELECT exec_time
FROM myTable
WHERE CASE WHEN exec_time NOT LIKE '%N%' THEN
split_part(exec_time,' ', 1)::int >= 60000
ELSE FALSE END
ORDER BY length(exec_time) desc, exec_time desc
LIMIT 10;
That being said, if 'None Recorded'
is the only case to rule out, use a faster check on the left:
exec_time NOT LIKE 'N%'
If the above are still errors, refer to this to find any offensive lines you may have missed:
SELECT DISTINCT exec_time
FROM myTable
WHERE exec_time NOT LIKE '%N%'
AND exec_time !~ '^\\d+ ' -- not all digits before the first space
In modern Postgres, you only need one backslash. '^\d+ '
! You seem to need to double the backslash in Redshift, which still uses the deprecated Posix string escape syntax for default strings and no explicit ( E'^\\d+ '
) declaration !
Generally, it is not good to mix data this way. You need to have a column to store runtime integer
. Much cheaper, cleaner and faster.
source to share
I think the problem is with the "None Recorded" value. I don't know if it will run the first where the first is excluded or not. Try the following:
SELECT exec_time
FROM (SELECT exec_time FROM myTable WHERE exec_time NOT LIKE 'N%') as foo
WHERE cast(split_part(foo.exec_time, ' ', 1) as int) >= 60000
ORDER by length(foo.exec_time) desc, foo.exec_time desc
limit 10
source to share