DBeaver - Match substring in DBeaver SQL Editor using Regular Expression. How to do it?-CodePudding

Look at the following examples:

PRJRNB060
BKRSTX001
GRDADJ001
PRJRNB094
GRDEDN248
PRJRNB001

I want to filter out the strings where the 4th, 5th, 6th character is RNB. The syntax is as follows:

SELECT * FROM [table_name] WHERE [column_name] LIKE '*regular expression*'

Can anyone suggest a regular expression which will look at the column and match if the 4th, 5th, 6th character is RNB and will filter out the table accordingly ? Thanks

CodePudding user response：

You may try this regex in your query:

^.{3}RNB.*$

Oracle query:

SELECT *
FROM table_name
WHERE REGEXP_LIKE (column_name,'^.{3}RNB.*$', 'i');

I have included 'i' as last parameter. This will make it case insensitive. You may skip this parameter if you don't want that.

Demo

CodePudding user response：

Oracle DB has REGEX_LIKE function, you can use that like this:
SELECT * FROM [table_name] WHERE REGEXP_LIKE(column_name, '^.{3}RNB.{3}$')

CodePudding user response：

You just can use SUBSTR() function within the where condition as

WHERE SUBSTR(col,4,3)='RNB'

in order to determine the substring starting from the fourth character, and the three succesive characters are taken to be matched with RNB

If you're looking for a case-insensitive match, then use as

WHERE UPPER(SUBSTR(col,4,3))='RNB'

CodePudding user response：

Query:

select * from table_name1 
where column_name like '___RNB%'

Sample:

create table table_name1 (column_name varchar2(30));
insert into table_name1 values ('PRJRNB060');
insert into table_name1 values ('BKRSTX001');
insert into table_name1 values ('GRDADJ001');
insert into table_name1 values ('PRJRNB094');
insert into table_name1 values ('GRDEDN248');
insert into table_name1 values ('PRJRNB001');

Result:

PRJRNB060
PRJRNB094
PRJRNB001