Home » SQL & PL/SQL » SQL & PL/SQL » like in sql
like in sql [message #235595] Mon, 07 May 2007 06:23 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

i have table like

name

a1111e111
111a111e111
abcd
abcde
1111111
222222

i want to o/p(only a&e commbination)
a1111e111
111a111e111
abcde

Re: like in sql [message #235596 is a reply to message #235595] Mon, 07 May 2007 06:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Try this:
with yourtable as
(
SELECT 'a1111e111'   thename FROM dual UNION ALL
SELECT '111a111e111' thename FROM dual UNION ALL
SELECT 'abcd'        thename FROM dual UNION ALL
SELECT 'abcde'       thename FROM dual UNION ALL
SELECT '1111111'     thename FROM dual UNION ALL
SELECT '222222'      thename FROM dual 
)
select thename
from yourtable
where thename like '%a%e%'


MHE
Re: like in sql [message #235598 is a reply to message #235596] Mon, 07 May 2007 06:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Or, if they can also appear in different order and mixed case:
 
where instr(upper(<your_column>), 'A') > 0
and   instr(upper(<your_column>), 'E') > 0
Re: like in sql [message #235599 is a reply to message #235596] Mon, 07 May 2007 06:34 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

iam missing 'edda'
i want this record also( i want combination of a&e and e&a



SELECT  thename FROM 
(
SELECT 'a1111e111'   thename FROM dual UNION ALL
SELECT '111a111e111' thename FROM dual UNION ALL
SELECT 'abcd'        thename FROM dual UNION ALL
SELECT 'abcde'       thename FROM dual UNION ALL
SELECT '1111111'     thename FROM dual UNION ALL
SELECT '222222'      thename FROM dual UNION ALL
SELECT  'edda'        thename FROM dual 
) WHERE  thename LIKE '%a%e%'



o/p

THENAME

a1111e111
111a111e111
abcde



Re: like in sql [message #235606 is a reply to message #235599] Mon, 07 May 2007 06:53 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
thnaks frank
Re: like in sql [message #235608 is a reply to message #235595] Mon, 07 May 2007 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You often use o/p in your question.
What does this mean?

Regards
Michel
Re: like in sql [message #235610 is a reply to message #235608] Mon, 07 May 2007 07:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Original poster
Re: like in sql [message #235613 is a reply to message #235610] Mon, 07 May 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Origina Poster" is the usual meaning but pavuluri asked:
Quote:
i want to o/p(only a&e commbination)

This is this meaning I wanted to know.
Regards
Michel
Re: like in sql [message #235615 is a reply to message #235613] Mon, 07 May 2007 07:33 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

i guess it is output.

regards,
shanth
Previous Topic: MAXEXTENTS in Index
Next Topic: how do i do incremental load using procedure
Goto Forum:
  


Current Time: Thu Dec 08 21:52:00 CST 2016

Total time taken to generate the page: 0.06810 seconds