| Exact String Match in Instr [message #641476] |
Sun, 16 August 2015 10:27  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Hi All,
I want to search exact match in Instr ..for example
Query:
with t as
(select '12345:1' a from dual
union
select '12345:2' a from dual
union
select '12345:112' a from dual
union
select '12345:10' a from dual)
select a, instr(a,'12345:1',1) b from t
I want Output like:
---a -------b---
12345:1 1
if string match then only query will return result otherwise its not. Please help me out ..
Thanks,
Xandot
[EDITED by LF: applied [code] tags to desired output]
[Updated on: Mon, 17 August 2015 00:12] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Exact String Match in Instr [message #641502 is a reply to message #641499] |
Mon, 17 August 2015 02:59  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Xandot wrote on Mon, 17 August 2015 08:53Problem is:
When I search '12345:10' string in query then its return me '12345:1' & '12345:10'. but it should be return only '12345:10'.
Show us the code that you're using, along with a test case of sample data and the expected results based on that sample data.
|
|
|
|
| Re: Exact String Match in Instr [message #641510 is a reply to message #641476] |
Mon, 17 August 2015 06:01 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Last time I checked exact match was called equality:
SQL> with t as
2 (select '12345:1' a from dual
3 union
4 select '12345:2' a from dual
5 union
6 select '12345:112' a from dual
7 union
8 select '12345:10' a from dual)
9 select a, 1 b from t where a = '12345:1'
10 /
A B
--------- ----------
12345:1 1
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Exact String Match in Instr [message #641549 is a reply to message #641548] |
Mon, 17 August 2015 13:07 |
 |
DK555
Messages: 5
Registered: August 2015
|
Junior Member |
|
|
No. I wouldn't go that far.
@Michel: Can you tell me why the below answer is silly,
with t as
(select '12345:1' a from dual
union
select '12345:2' a from dual
union
select '12345:112' a from dual
union
select '12345:10' a from dual)
select a, instr(a,'&&b',1) b from t where a=(select '&b' from dual);
It gets the input from user. User gets the required output for any input.It passes all testcases. If his code is in PLSQL, he has to declare a DEFINE variable for this, thats it.
I will point out the problem in your solution as below,
select * from t where regexp_like(a, '^12345:1$')
is the same as below,
select a, 1 b from t where a = '12345:1'
Just that the above uses a regex function with a hardcoded value and returns output only for 1 scenario. The test-case will fail for 12345:2 and 12345:112 and 12345:10 . Impress me if otherwise
|
|
|
|
| Re: Exact String Match in Instr [message #641550 is a reply to message #641549] |
Mon, 17 August 2015 13:12 |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Can you tell me why the below answer is silly,
See my previous answer and try to think about it.
Quote:I will point out the problem in your solution as below,... is the same as below,...
I know this is why I laughed when Solomon posted his remark.
But, you, don't you think that
a=(select '12345:1' from dual)
is the same than
a = '12345:1'
?
[Updated on: Mon, 17 August 2015 13:13] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Exact String Match in Instr [message #641553 is a reply to message #641551] |
Mon, 17 August 2015 15:05 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
DK555 wrote on Mon, 17 August 2015 14:23Both are same.
They are same result-wise but not execution plan/performance wise. Compare:
SQL> explain plan for
2 select a, 1 b from tbl where a = '12345:1';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2144214008
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TBL | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
1 - filter("A"='12345:1')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
SQL> explain plan for
2 select a, 1 b from tbl where a in (select '12345:1' from dual);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3031842229
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TBL | 4 | 24 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE
:B1='12345:1'))
3 - filter(:B1='12345:1')
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: Exact String Match in Instr [message #641556 is a reply to message #641555] |
Tue, 18 August 2015 00:30 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Don't trust reach word Xandot post for his question.
@Xandot who should think to feedback and thank (here and in his previous topics) if he wants to be helped again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
