Finding numeric and alpha vs just alpha data query

Question

Finding numeric and alpha vs just alpha data query

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
September 7, 2015 at 7:23 am

#311409

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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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;-)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)