Skip to Content
avatar image
Former Member

Using between operator for string fields

Hi

The data in the table is stored as nvarchar. The operation I would want to perform is "between". Ex: AB1001, AB1002, AB1003, SN1002345, SN1002346 etc..

Would like to search from AB1001 to AB1010.

Result should be AB1001, AB1002, AB1003.

Is it possible with a Between operator / regular expression?

Thank you

Regards

Vidhya

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Mar 24, 2017 at 02:18 PM

    I didn't profile it but you have something to start with and can easily be extended.

    remove references to "rdwdbo". my test schema.

    drop table rdwdbo.test_alphnum_between ;
    
    
    create table rdwdbo.test_alphnum_between ( col1 nvarchar(128));
    
    
    insert into rdwdbo.test_alphnum_between values  ('AB1001');
    insert into rdwdbo.test_alphnum_between values  ('AB1002');
    insert into rdwdbo.test_alphnum_between values  ('AB1003');
    insert into rdwdbo.test_alphnum_between values  ('AB1004');
    insert into rdwdbo.test_alphnum_between values  ('AB1005');
    insert into rdwdbo.test_alphnum_between values  ('AB01005');
    insert into rdwdbo.test_alphnum_between values  ('AB01005');
    insert into rdwdbo.test_alphnum_between values  ('AB10005');
    insert into rdwdbo.test_alphnum_between values  ('SN1001234');
    insert into rdwdbo.test_alphnum_between values  ('SN1002345');
    insert into rdwdbo.test_alphnum_between values  ('SN1002346');
    
    
    
    
    SELECT * 
    FROM rdwdbo.test_alphnum_between
    WHERE COL1 BETWEEN SUBSTR_REGEXPR('([:digit:].*?[:digit:])' IN COL1 ) 
    
    
    
    
    DROP PROCEDURE rdwdbo.p_test_alphnum_between ;
    CREATE PROCEDURE rdwdbo.p_test_alphnum_between ( IN vIn_beg nvarchar(128), IN vIn_end nvarchar(128) )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    DEFAULT SCHEMA RDWDBO
    AS
    BEGIN
    
    
    -- assumes the prefixes match (i.e. parms like AB12345 and AB23456 and not AB123 and SB123)
    SELECT COL1, CAST(SUBSTR_REGEXPR('(\d+)' IN COL1 ) AS INT) col_digi, CAST(SUBSTR_REGEXPR('(\d+)' IN :vIn_beg ) AS INT) as ":vIn_beg", CAST(SUBSTR_REGEXPR('(\d+)' IN :vIn_end ) AS INT) as ":vIn_end"
         , SUBSTR_REGEXPR('^([A-Z]+)(\d+)'  IN COL1 GROUP 1 ) AS PREFIX
         , SUBSTR_REGEXPR('^([A-Z]+)(\d+)'  IN COL1 GROUP 2 ) AS SUFFIX
      FROM rdwdbo.test_alphnum_between
     WHERE SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN COL1 GROUP 1 ) = SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN :vIn_beg GROUP 1 )
       AND CAST(SUBSTR_REGEXPR('(\d+)' IN COL1 ) AS INT) BETWEEN CAST(SUBSTR_REGEXPR('(\d+)' IN :vIn_beg ) AS INT) AND CAST(SUBSTR_REGEXPR('(\d+)' IN :vIn_end ) AS INT);
    
    
    END;
    
    
    SELECT COL1
         , SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN COL1 GROUP 1 ) col1_alpha
         , CAST(SUBSTR_REGEXPR('(\d+)' IN COL1 ) AS INT) col1_digi
      FROM rdwdbo.test_alphnum_between order by CAST(SUBSTR_REGEXPR('(\d+)' IN COL1 ) AS INT);
    
    
    call rdwdbo.p_test_alphnum_between ('AB1001', 'AB1003');  --AB1001 AB1002 AB1003
    call rdwdbo.p_test_alphnum_between ('AB1001', 'AB10005'); -- AB1001 AB1002 AB1003 AB1004 AB1005 AB01005 AB01005 AB10005
    call rdwdbo.p_test_alphnum_between ('SN1001234', 'SN1001235') -- SN1001234
    
    
    call rdwdbo.p_test_alphnum_between ( 'SN1002345', 'SN1002346');
    call rdwdbo.p_test_alphnum_between ( 'AB1001', 'AB1010');
    
    
    ------------------------------------------------------------------------------------------
    -- function version
    ------------------------------------------------------------------------------------------
    
    
    DROP FUNCTION rdwdbo.fn_test_alphnum_between ;
    
    
    CREATE FUNCTION rdwdbo.fn_test_alphnum_between ( IN vIn_beg nvarchar(128), IN vIn_end nvarchar(128) )
      RETURNS table ( COL1 NVARCHAR(128), ALPHA_PART NVARCHAR(128), NBR_PART INT )
      LANGUAGE SQLSCRIPT
      SQL SECURITY INVOKER
      DEFAULT SCHEMA RDWDBO
    AS
    BEGIN
        -- assumes the prefixes match (i.e. parms like AB12345 and AB23456 and not AB123 and SB123)
    RETURN
        SELECT COL1
             , SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN COL1 GROUP 1 ) AS ALPHA_PART
             , SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN COL1 GROUP 2 ) AS NBR_PART
          FROM rdwdbo.test_alphnum_between
         WHERE SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN COL1 GROUP 1 ) = SUBSTR_REGEXPR('^([A-Z]+)(\d+)' IN :vIn_beg GROUP 1 )
           AND CAST(SUBSTR_REGEXPR('(\d+)' IN COL1 ) AS INT) BETWEEN CAST(SUBSTR_REGEXPR('(\d+)' IN :vIn_beg ) AS INT) AND CAST(SUBSTR_REGEXPR('(\d+)' IN :vIn_end ) AS INT);
    
    
    END;
    
    
    select * from rdwdbo.fn_test_alphnum_between ('AB1001', 'AB1003');  --AB1001 AB1002 AB1003
    select * from rdwdbo.fn_test_alphnum_between ('AB1001', 'AB10005'); -- AB1001 AB1002 AB1003 AB1004 AB1005 AB01005 AB01005 AB10005
    select * from rdwdbo.fn_test_alphnum_between ('SN1001234', 'SN1001235') -- SN1001234
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 24, 2017 at 10:51 AM

    In Order to get a numeric comparison semantic on your character data, you will need to extract the numeric part first and perform the range condition against the extracted numbers.

    Add comment
    10|10000 characters needed characters exceeded