karen.blake (9/7/2015)
Hi All,Will try and explain this better.
We have a SAL field in the database that has numeric and alpha and just alpha data in it.
I need to get a count of both so I have the below query but it's not working as it should be (as I am sanity checking the data it pulls).
Query is this:
selectcount(distinct a.APP_ID)
from app_table a
join v_table v on v.ag_id = a.ag_id
where a.create_date >= add_months(sysdate, -3)
and REGEXP_LIKE(v.SAL, '[^0-9$]')
and a.app_site_cc = 'UK';
The data in a field would be like this (thought some examples might help):
Numeric - £30-40 per hour, sal
Non-numeric - Negotiable
Can anyone help at all? I did have a good old google but as an MS-SQL gal this is trickier for me.
Many thanks in advance.
With the understanding that I've not written a lick of code for Oracle in more than a decade and to include a 3rd eventuality, something like the following would do the trick in SQL Server. I don't know if Oracle is going to get all picky about there being aggregate functions with no GROUP BY, though.
SELECT SUM(CASE WHEN v.sal NOT LIKE '%[0-9$]%' THEN 1 ELSE 0 END) AS alpha_only,
SUM(CASE WHEN v.sal LIKE '%[0-9$]%[a-zA-Z]%'
OR v.sal LIKE '%[a-zA-Z]%[0-9$]%' THEN 1 ELSE 0 END) AS alphanumeric,
SUM(CASE WHEN v.sal NOT LIKE '%[^0-9$]%' THEN 1 ELSE 0 END) AS numeric_only
FROM app_table a
JOIN v_table v on v.ag_id = a.ag_id
WHERE a.create_date >= add_months(sysdate, -3)
AND a.app_site_cc = 'UK'
;
And, I also don't know if you actually need to use the REGEXP_LIKE function, which is likely to be much more expensive performance-wise than a simple ANSI LIKE.
There might also be an "IsAlphaOnly" and similar functions in Oracle that I just don't know about that might do the job even better.
--Jeff Moden
Change is inevitable... Change for the better is not.