Home » SQL & PL/SQL » SQL & PL/SQL » How to display the letter starting with "B" followed by number only. (Oracle 11g)
How to display the letter starting with "B" followed by number only. [message #605441] Thu, 09 January 2014 07:20 Go to next message
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 #605444 is a reply to message #605441] Thu, 09 January 2014 07:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
SQL> 
SQL> SELECT *
  2    FROM BADGE_DTLS
  3   WHERE REGEXP_LIKE(BADGE_ID, '^B[[:digit:]]')
  4     AND NOT REGEXP_LIKE(BADGE_ID, '[[:punct:]]');
 
BADGE_ID
--------------------
B12345
B34568


My 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.

[Updated on: Tue, 11 March 2014 13:27] by Moderator

Report message to a moderator

Re: How to display the letter starting with "B" followed by number only. [message #605447 is a reply to message #605444] Thu, 09 January 2014 07:53 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks Lalit
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Lalit Kumar B wrote on Thu, 09 January 2014 08:45
My 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 #605459 is a reply to message #605458] Thu, 09 January 2014 09:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Now, assuming BADGE_ID format is letter B followed only by one or more digits, I wouldn't use regular expressions and would use:

WHERE RTRIM(BADGE_ID,'0123456789') = 'B'


And if BADGE_ID is indexed, I'd use:

WHERE BADGE_ID LIKE 'B_%'
  AND RTRIM(BADGE_ID,'0123456789') = 'B'


SY.

[Updated on: Thu, 09 January 2014 09:33]

Report message to a moderator

Re: How to display the letter starting with "B" followed by number only. [message #605460 is a reply to message #605459] Thu, 09 January 2014 09:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, I am wrong with just:

WHERE RTRIM(BADGE_ID,'0123456789') = 'B'


since it allows BADGE_ID = 'B'.

SY.
Re: How to display the letter starting with "B" followed by number only. [message #605461 is a reply to message #605460] Thu, 09 January 2014 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WHERE RTRIM(BADGE_ID,'0123456789') = 'B'
AND LENGTH(BADGE_ID) > 1
Re: How to display the letter starting with "B" followed by number only. [message #605470 is a reply to message #605461] Thu, 09 January 2014 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

/forum/fa/2115/0/

Re: How to display the letter starting with "B" followed by number only. [message #605505 is a reply to message #605461] Fri, 10 January 2014 01:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Since OP asked for regular expression query, I posted that not so good query.

Else, I would go with :
SQL> SELECT *
  2    FROM BADGE_DTLS
  3   WHERE TRANSLATE(BADGE_ID, 'B0123456789', '0') = '0'
  4     AND LENGTH(BADGE_ID) > 1;
 
BADGE_ID
--------------------
B12345
B34568
Re: How to display the letter starting with "B" followed by number only. [message #605508 is a reply to message #605505] Fri, 10 January 2014 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wrong!
SQL> select 'good' from dual where translate('123B45','B0123456789', '0') = '0';
'GOO
----
good

1 row selected.

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 Go to previous messageGo to next message
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
Re: How to display the letter starting with "B" followed by number only. [message #605515 is a reply to message #605512] Fri, 10 January 2014 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SUBSTR(BADGE_ID, 0, 1)


The index in a string starts with 1.
It is bad programming to use a side effect of SUBSTR that allows to use 0 for 1.

(From documentation: "If position is 0, then it is treated as 1.")

Re: How to display the letter starting with "B" followed by number only. [message #605526 is a reply to message #605515] Fri, 10 January 2014 05:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And either SUBSTR(BADGE_ID, 1, 1) = 'B' or LENGTH(BADGE_ID) > 1 will prevent optimizer from using index on BADGE_ID. That's why I suggested:

BADGE_ID LIKE 'B_%'


which combines above two conditions and allows optimizer to use index on BADGE_ID.

SY.
Re: How to display the letter starting with "B" followed by number only. [message #605527 is a reply to message #605526] Fri, 10 January 2014 05:22 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
More homework. What happened to "What have you tried so far?" Or were you all too keen to show off your REGEXP skills? Wink
Re: How to display the letter starting with "B" followed by number only. [message #673464 is a reply to message #605527] Tue, 20 November 2018 10:58 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Ironically, I had forgotten all about this thread. I just found it searching the site. I have been trying to fix a similar problem regarding validating user ids. Thanks for all your help guys Very Happy

And apologies for the homework remark! I must have been having a bad day Embarassed

[Updated on: Tue, 20 November 2018 11:01]

Report message to a moderator

Previous Topic: Are following statements executed in any case if the first one throws an exception?
Next Topic: SQL Query assistance
Goto Forum:
  


Current Time: Thu Mar 28 15:31:21 CDT 2024