Finding numeric and alpha vs just alpha data query

  • 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.

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

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

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

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