Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
3 replies Latest Post - ‏2012-10-17T10:46:04Z by SystemAdmin
SystemAdmin
SystemAdmin
1731 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Please help with a query

    ‏2012-10-16T14:24:05Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Please help with a query

      ‏2012-10-16T16:36:19Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Please help with a query

        ‏2012-10-17T10:46:04Z  in response to SystemAdmin
        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