SQL Query help

glennbtn · 08-04-2022, 09:37 AM

Hi All

So I have a query I have written for my pbx to get data out, the script here works perfectly

Code:

select caller_id_number,caller_destination,destination_number,start_stamp from today_call_records WHERE start_stamp > now() - interval 5 minute AND direction = 'inbound' AND domain_name = 'pbx.mydomain.co.uk' AND destination_number NOT regexp '^([1000-1999])' AND destination_number NOT regexp '^([*90])' AND destination_number NOT regexp '^([*89])' AND destination_number NOT regexp '^([*88])' AND destination_number NOT regexp '^([*87])' AND destination_number NOT regexp '^([*86])' AND destination_number NOT regexp '^([*85])' AND destination_number NOT regexp '^([*84])' AND destination_number NOT regexp '^([*83])' AND hangup_cause NOT like 'USER_BUSY' ORDER BY start_stamp

Now I also need to exclude from the sql query *82 *81 & *80 but if I add any more eg another

Code:

AND destination_number NOT regexp '^([*82])'

Then I don't get any errors but also don't get any data. As soon as I remove the extra bit of query it works fine.

Is there some sort of limitation in the queries or is there anything I can do to resolve the issue

Thanks

boughtonp · 08-04-2022, 09:51 AM

I suspect the solution can be one or two simple regexes, but I'm making no attempt to read that query until you format it and use "[code]..[/code]" tags to preserve the formatting.

glennbtn · 08-04-2022, 10:01 AM

Oops, sorry not put code in before.

I have edited the original post

Thanks

pan64 · 08-04-2022, 10:03 AM

would be nice to know where did you enter this command. Is it command line? script? or ?

glennbtn · 08-04-2022, 10:17 AM

I have it in both a php script and also test it using table plus

chrism01 · 08-04-2022, 11:52 PM

Your 'extra' regexp is missing the end single-quote.

glennbtn · 08-05-2022, 02:13 AM

Hi chrism01

From the above yes I forgot the extra single quote but that's not missing when I add further examples. My issue is if I add anymore to the original query IE and extras

Code:

AND destination_number NOT regexp '^([*82])'

I fail to get anything from the database and no errors.

pan64 · 08-05-2022, 02:20 AM

what you posted is still not enough to diagnose. I can only guess: somehow/somewhere there was a line length limit set.
posting parts of a line without knowing anything about the context/environment is simply useless.
http://www.catb.org/~esr/faqs/smart-...html#beprecise

lvm_ · 08-05-2022, 02:36 AM

Regular expression ^([*82]) means 'all lines starting with *, 8 or 2 - is this what you really want? It's also a good idea to mention what kind of SQL you use and what flavour of regular expression in supports.

glennbtn · 08-05-2022, 02:47 AM

@pan64 the whole sql query is at the root of the question, nothing is missing from the query and it's not just part of a query

@lvm We are trying to track missed calls as the system does not do it correctly. From the report we need to exclude all the instances of eg *81 *82 *83 etc as these are features codes we have built in and are not missed calls as the customer has chosen them.

lvm_ · 08-05-2022, 03:16 AM

You didn't make it much clearer, but if by this you mean CDRs starting with '*81', '*82' and so on then your regexp should be '^\*8[1-3]', not what you typed. Wikipedia has a nice article about regular expressions, read it.

glennbtn · 08-05-2022, 03:40 AM

@lvm thanks. I did forget to put in that it was mariadb 10.1.48 sorry

I can't currently get it to work as getting a regex error but will go and take a look and try and get my head around it all.

Thanks again

boughtonp · 08-05-2022, 07:55 AM

Quote:

Originally Posted by glennbtn

I have edited the original post

All you've done is wrapped a query with no formatting in code tags.

The code tags preserve formatting (i.e. newlines and indents), you still need to add this to make it readable in the first place.