Topic
  • 3 replies
  • Latest Post - ‏2012-10-17T10:46:04Z by SystemAdmin
SystemAdmin
SystemAdmin
1731 Posts

Pinned topic Please help with a query

‏2012-10-16T13:53:42Z |
I am trying to select rows where a column called asset contains any number of alpha chars then an _ then any number. Some valid examples are dirt_1, AAAA_3456563, sdlfkjs_3545634. Some invalid examples would be 845_abc, ab3_z12, 1234_1231. Any help would be appreciated. Thank you.
Updated on 2012-10-17T10:46:04Z at 2012-10-17T10:46:04Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: Please help with a query

    ‏2012-10-16T14:24:05Z  
    Hi there,

    You need to be more specific here. Your question does not make sense unless you provide a context.

    LooW
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: Please help with a query

    ‏2012-10-16T16:36:19Z  
    Hi there,

    You need to be more specific here. Your question does not make sense unless you provide a context.

    LooW
    I have a table defined like

    CREATE TABLE asset
    (asset_name char (30));

    asset_name will contain a variety of letters, numbers and symbols. I need to be able to select the rows that contain letter or letters, then an underscore, then a number or numbers.

    Does this info make things more clear?
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: Please help with a query

    ‏2012-10-17T10:46:04Z  
    I have a table defined like

    CREATE TABLE asset
    (asset_name char (30));

    asset_name will contain a variety of letters, numbers and symbols. I need to be able to select the rows that contain letter or letters, then an underscore, then a number or numbers.

    Does this info make things more clear?
    Hi Stacy5,

    Yes that clarifies your answer.

    As a quick answer I can propose you the creation of two functions to check the each two parts that form the asset_name. The first function would check if the right part is a not number char sequence and the second would check if the left part is a number.

    You may need to adapt the functions to your own needs but they work with the example provided.

    First function:
    
    CREATE OR REPLACE FUNCTION IsNumber(p_number CHAR(30)) RETURNS SMALLINT LANGUAGE SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN DECLARE SQLCODE INT DEFAULT 0; DECLARE vIsNumber SMALLINT; DECLARE vNUMBER DECFLOAT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN IF SQLCODE=-420 THEN SET vIsNumber=0; END IF; END; SET vIsNumber=1; SET vNUMBER=DECFLOAT(p_number); RETURN vIsNumber; END@
    


    Second function:
    
    CREATE OR REPLACE FUNCTION IsNotNumber(p_char CHAR(30)) RETURNS SMALLINT LANGUAGE SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN DECLARE SQLCODE INT DEFAULT 0; DECLARE vIsNotNumber INT; DECLARE vNUMBER DECFLOAT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN IF SQLCODE=-420 THEN SET vIsNotNumber=1; END IF; END; SET vIsNotNumber=0; SET vNUMBER=DECFLOAT(p_char); RETURN vIsNotNumber; END@
    


    Now for the testing and query:
    
    CREATE TABLE asset(asset_name CHAR(30))@   INSERT INTO asset VALUES(
    'dirt_1')@ INSERT INTO asset VALUES(
    'AAAA_3456563')@ INSERT INTO asset VALUES(
    'sdlfkjs_3545634')@   INSERT INTO asset VALUES(
    '845_abc')@ INSERT INTO asset VALUES(
    'ab3_z12')@ INSERT INTO asset VALUES(
    '1234_1231')@   SELECT asset_name FROM asset WHERE INSTR(asset_name,
    '_') > 0 AND IsNotNumber(SUBSTR(asset_name,1,INSTR(asset_name,
    '_')-1))=1 AND IsNumber(SUBSTR(asset_name,INSTR(asset_name,
    '_')+1))=1@     ASSET_NAME ------------------------------ dirt_1 AAAA_3456563 sdlfkjs_3545634   3 record(s) selected.
    


    Hope it helps.

    LooW