Home » SQL & PL/SQL » SQL & PL/SQL » How to write query for desire output?
| How to write query for desire output? [message #611192] |
Sat, 29 March 2014 04:08  |
 |
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
|
Senior Member |
|
|
I will have to choose only those row who contain single 2 from given column col_id, please write sql qury here:
Col_id
11, 2, 3, 22
5, 6, 12, 22
4, 23, 14, 22
2, 9, 22, 30
8, 18, 19, 2
Output:
Col_id
11, 2, 3, 22
2, 9, 22, 30
8, 18, 19, 2
[Updated on: Sat, 29 March 2014 04:09] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to write query for desire output? [message #611688 is a reply to message #611195] |
Mon, 07 April 2014 03:51  |
 |
gauravgautam135
Messages: 33
Registered: December 2013
|
Member |
|
|
Hi Michel,
I think we can do this by simple LIKE statement.
Please see below scenarios and let me know if there is any problem with it.
Assumption:
The input data obtained from column will be in similar fashion as mentioned by OP and is string by type.
Possible Scenarios:
1. '2' coming at start i.e. the column will always start with '2,'.
2. '2' coming in middle i.e. the column will always have ', 2,' in it.
3. '2' coming in end i.e. the column will always ends with ' ,2'.
Since we need to find out those rows which have '2' as a separate value.
SQL> WITH tab1 AS
2 (SELECT '1, 2, 3, 22' col FROM dual
3 UNION ALL
4 SELECT '5, 6, 12, 22' FROM dual
5 UNION ALL
6 SELECT '4, 23, 14, 22' FROM dual
7 UNION ALL
8 SELECT '2, 9, 22, 30' FROM dual
9 UNION ALL
10 SELECT '8, 18, 19, 2' FROM dual)
11 SELECT * FROM tab1 WHERE col LIKE '%, 2,%' OR col LIKE '2,%' OR col LIKE '%, 2';
COL
-------------
1, 2, 3, 22
2, 9, 22, 30
8, 18, 19, 2
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Tue May 07 12:12:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
