Skip to Content
0

Using between operator for string fields

Mar 24, 2017 at 09:15 AM

82

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Steven Perry Mar 24, 2017 at 02:18 PM
0

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


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Steven. I was able to extend the above code to filter the range for alphanumeric strings

0
Lars Breddemann
Mar 24, 2017 at 10:51 AM
1

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.

Share
10 |10000 characters needed characters left characters exceeded