September 7, 2015 at 7:23 am
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:
select
count(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.
September 8, 2015 at 1:15 am
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:
select
count(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.
Can you elaborate a little further on this, not certain what the question really is?
Assume you are running this query on an Oracle as it wouldn't run on SQL Server;-)
September 8, 2015 at 6:48 am
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:
select
count(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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy