How to display the letter starting with "B" followed by number only. [message #605441] |
Thu, 09 January 2014 07:20 |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
Please provide the regular expression query
to find the BADGE_ID starting with B and should be followed only by number.
CREATE TABLE BADGE_DTLS(
BADGE_ID VARCHAR2(20));
INSERT INTO BADGE_DTLS VALUES('B12345');
INSERT INTO BADGE_DTLS VALUES('B34568');
INSERT INTO BADGE_DTLS VALUES('B55667*');
INSERT INTO BADGE_DTLS VALUES('C58908');
INSERT INTO BADGE_DTLS VALUES('B*12345');
INSERT INTO BADGE_DTLS VALUES('*B12345*');
INSERT INTO BADGE_DTLS VALUES('-B12345');
The output should be.
B12345
B34568
Please help me.
Thanks.
|
|
|
|
|
Re: How to display the letter starting with "B" followed by number only. [message #605458 is a reply to message #605444] |
Thu, 09 January 2014 09:21 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 09 January 2014 08:45My above solution might not be the perfect one, since, I think the two conditions could be merged into a single condition. Someone may do that perfectly.
And why did you come up with two conditions in the first place? And, btw, what that second condition for? OP wants to find the BADGE_ID starting with B and should be followed only by number. First of all, it is misleading. Does is mean BADGE_ID format is letter B followed by any (> 0) number of digits or letter be followed by a digit and then doesn't matter? If former, then:
WHERE REGEXP_LIKE(BADGE_ID,'^B\d+$')
should be enough. And for latter, all we need is to remove +$:
WHERE REGEXP_LIKE(BADGE_ID,'^B\d')
SY.
[Updated on: Thu, 09 January 2014 09:22] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: How to display the letter starting with "B" followed by number only. [message #605512 is a reply to message #605508] |
Fri, 10 January 2014 02:44 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 10 January 2014 13:39
Wrong!
Silly me, I missed the point the string must start with letter 'B'. I can add SUBSTR and it will fix it, however, RTRIM looks quite simple against TRANSLATE and SUBSTR. Though, explain plan is almost same. Surprisingly, CPU cost is a bit more for RTRIM query. Anybody can validate that by checking the explain plan. I am using PL/SQL developer to view the PLAN_TABLE.
SQL> SELECT *
2 FROM BADGE_DTLS
3 WHERE TRANSLATE(BADGE_ID, 'B0123456789', '0') = '0'
4 AND SUBSTR(BADGE_ID, 0, 1) = 'B'
5 AND LENGTH(BADGE_ID) > 1;
BADGE_ID
--------------------
B12345
B34568
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'a' FOR SELECT *
2 FROM BADGE_DTLS
3 WHERE TRANSLATE(BADGE_ID, 'B0123456789', '0') = '0'
4 AND SUBSTR(BADGE_ID, 0, 1) = 'B'
5 AND LENGTH(BADGE_ID) > 1;
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','a','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 226389855
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BADGE_DTLS | 2 | 24 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / BADGE_DTLS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LENGTH("BADGE_ID")>1 AND SUBSTR("BADGE_ID",0,1)='B' AND
TRANSLATE("BADGE_ID",'B0123456789','0')='0')
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "BADGE_ID"[VARCHAR2,20]
Note
-----
- dynamic sampling used for this statement (level=2)
28 rows selected
SQL> SELECT *
2 FROM BADGE_DTLS
3 WHERE RTRIM(BADGE_ID, '0123456789') = 'B'
4 AND LENGTH(BADGE_ID) > 1;
BADGE_ID
--------------------
B12345
B34568
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'b' FOR SELECT *
2 FROM BADGE_DTLS
3 WHERE RTRIM(BADGE_ID, '0123456789') = 'B'
4 AND LENGTH(BADGE_ID) > 1;
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','b','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 226389855
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BADGE_DTLS | 2 | 24 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / BADGE_DTLS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LENGTH("BADGE_ID")>1 AND
RTRIM("BADGE_ID",'0123456789')='B')
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "BADGE_ID"[VARCHAR2,20]
Note
-----
- dynamic sampling used for this statement (level=2)
28 rows selected
|
|
|
|
|
|
|