Home » SQL & PL/SQL » SQL & PL/SQL » Find names containing letters (Merged)
Find names containing letters (Merged) [message #258760] Mon, 13 August 2007 09:38 Go to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Dear friends,

i want a sql query for this example:

To display a name in a table having both the letters s & v either at starting or ending or intermediate ....

This requirement is urgent anybody help me..



Regards,

Arun..


[Mod-Edit: changed childish change to topic-title]

[Updated on: Mon, 13 August 2007 12:28] by Moderator

Report message to a moderator

Re: Sql query urgent [message #258762 is a reply to message #258760] Mon, 13 August 2007 09:42 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Nothing on this forum is URGENT.

If it is truly that urgent, hire a consultant.

Depending on your database version you can accomplish your goal with using the INSTR function or perhaps REGEX.

Re: Sql query urgent [message #258764 is a reply to message #258760] Mon, 13 August 2007 09:43 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

my version is 10g.
Re: Sql query urgent [message #258765 is a reply to message #258760] Mon, 13 August 2007 09:44 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Make you subject line descriptive!!!


Use instr or like, it will work in any version.

if "v" has to follow "s" then

where name like '%s%v%'

if the order doesn't matter

where instr(name,'s') > 0 and instr(name,'v') > 0
Re: Sql query urgent [message #258767 is a reply to message #258760] Mon, 13 August 2007 09:51 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Dear Bill

for example :

Name
----
sankar
veera
ramesh
yoha
andrew
sarvesh

my output is:

sankar
veera
sarvesh


these output are having 's' or 'v' or both 's' and 'v'.this is my requirements.


please help me..

[Updated on: Mon, 13 August 2007 09:54]

Report message to a moderator

Re: Sql query urgent [message #258770 is a reply to message #258760] Mon, 13 August 2007 09:55 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
He Gave you the code above, read it and try it yourself.

What else do you want ?
Re: Sql query urgent [message #258771 is a reply to message #258760] Mon, 13 August 2007 10:00 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

i tried that query

where name like'%s%v%';

these query not display starting letter 's' and 'v'.this is problem

suresh is not display.
veera is not display.

mverasp is display in output.

mverasp is one name in my table
Re: Sql query urgent [message #258774 is a reply to message #258760] Mon, 13 August 2007 10:03 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Read his ENTIRE answer
Re: Sql query urgent [message #258775 is a reply to message #258760] Mon, 13 August 2007 10:05 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
i WILL GIVE YOU A HINT...If you want ANY string with "s" or "v" in it use OR instead of AND. Figure it out!
Re: Sql query urgent [message #258776 is a reply to message #258760] Mon, 13 August 2007 10:05 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

please give a query .
am not oracle expert..
please
Re: Sql query urgent [message #258778 is a reply to message #258771] Mon, 13 August 2007 10:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

To display a name in a table having both the letters s & v

suresh is not display.
veera is not display.



suresh and veeradon't have both the letters.

For havens sake, at least try to get straight what you actually want.

You don't have to be an oracle expert for THAT, just some common sense.
Re: Sql query urgent [message #258779 is a reply to message #258760] Mon, 13 August 2007 10:08 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

My column:

Name
----
sankar
veera
ramesh
yoha
andrew
kumarsarvesh

my output is:

sankar
veera
kumarsarvesh


these output are having 's' or 'v' or both 's' and 'v'.this is my requirements.


suresh is starting letter 's'
veera is starting letter 'v'
kumarsarvesh having both 's' and 'v'

ok ok..

i need query..

[Updated on: Mon, 13 August 2007 10:10]

Report message to a moderator

Re: Sql query urgent [message #258780 is a reply to message #258760] Mon, 13 August 2007 10:09 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
select name
from mytable
where instr(name,'s') > 0 OR instr(name,'v') > 0;

[Updated on: Mon, 13 August 2007 10:09]

Report message to a moderator

Re: Sql query urgent [message #258781 is a reply to message #258776] Mon, 13 August 2007 10:10 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

It's too simple --
Use the condition

WHERE NAME LIKE '%s%' OR '%v%'


Cheers
Sanka

[Updated on: Mon, 13 August 2007 10:11]

Report message to a moderator

Re: Sql query urgent [message #258782 is a reply to message #258760] Mon, 13 August 2007 10:11 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

My column:

Name
----
sankar
veera
ramesh
yoha
andrew
kumarsarvesh

my output is:

sankar
veera
kumarsarvesh


these output are having 's' or 'v' or both 's' and 'v'.this is my requirements.


suresh is starting letter 's'
veera is starting letter 'v'
kumarsarvesh having both 's' and 'v'

ok ok..

i need query..



Re: Sql query urgent [message #258783 is a reply to message #258781] Mon, 13 August 2007 10:12 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
sanka_yanka wrote on Mon, 13 August 2007 10:10
It's too simple --
Use the condition

WHERE NAME LIKE '%s%' OR '%v%'


Cheers
Sanka

Close...

WHERE NAME LIKE '%s%' OR NAME LIKE '%V%'


Re: Sql query urgent [message #258784 is a reply to message #258760] Mon, 13 August 2007 10:16 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If you care about case, then add the lower function

WHERE lower(NAME) LIKE '%s%' OR lower(NAME) LIKE  '%v%'

[Updated on: Mon, 13 August 2007 10:23]

Report message to a moderator

Re: Sql query urgent [message #258791 is a reply to message #258776] Mon, 13 August 2007 10:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
akkumar81 wrote on Mon, 13 August 2007 17:05
please give a query .
am not oracle expert..
please

You will never become one if all you do is come here and ask for queries you can copy verbatim.
You really have to put in some effort yourself!
icon9.gif  Re: Sql query urgent [message #258794 is a reply to message #258791] Mon, 13 August 2007 10:32 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Lazy Boy.../fa/1600/0/
Sql Query (very Urgent) [message #258798 is a reply to message #258760] Mon, 13 August 2007 10:43 Go to previous messageGo to next message
sdvenk
Messages: 5
Registered: August 2007
Junior Member
Hi friends.

Can u please help out me in solving this query by the under given example...

In a table, there are many names in a specefic colomns...
I need to select the names which has the characters 's','v','n'.

The query must be a single line query

Please help me out...

regards,
Venkat
Re: Sql Query (very Urgent) [message #258801 is a reply to message #258798] Mon, 13 August 2007 10:50 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

see previous forums..
Re: Sql Query (very Urgent) [message #258802 is a reply to message #258798] Mon, 13 August 2007 10:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What is going on here, are you in the same interview?

http://www.orafaq.com/forum/t/87305/69655/
Re: Sql Query (very Urgent) [message #258805 is a reply to message #258802] Mon, 13 August 2007 10:56 Go to previous messageGo to next message
sdvenk
Messages: 5
Registered: August 2007
Junior Member
Thomas,

We need this query for a project done by us...
Please help me out...

the qury that u gave to arun are not of single line query its working but we need a single line query...

For Eg.
SELECT * FROM V12 WHERE
NAME LIKE '%[VS]%'
its not working in oracle 10g but not in sql
Re: Sql Query (very Urgent) [message #258808 is a reply to message #258805] Mon, 13 August 2007 10:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then put it on a single line, if you need it on a single line.

SELECT * FROM V12 WHERE NAME LIKE '%[VS]%'


And I have absolutely no clue what you mean with 'its not working in oracle 10g but not in sql'
Re: Sql Query (very Urgent) [message #258811 is a reply to message #258808] Mon, 13 August 2007 11:02 Go to previous messageGo to next message
sdvenk
Messages: 5
Registered: August 2007
Junior Member
Thomas,

I tried that also...

Not working...can u plaese help is there is any other queries to solve this case...

Regards
Venkat
Re: Sql Query (very Urgent) [message #258812 is a reply to message #258798] Mon, 13 August 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see anything urgent in that question.

Interview question should be in "Homework, Training & Certification" forum.

Regards
Michel
Re: Sql Query (very Urgent) [message #258816 is a reply to message #258812] Mon, 13 August 2007 11:04 Go to previous messageGo to next message
sdvenk
Messages: 5
Registered: August 2007
Junior Member
Michel,

You see this query is something important in a large query...

So please tell me some alter solution


venkat
Re: Sql Query (very Urgent) [message #258817 is a reply to message #258812] Mon, 13 August 2007 11:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And if you really do an "oracle project" without having the slightest clue about oracle, then it's your own fault.

So if it's really a project go to the people who pay you and tell them "sorry, we can't do it."

If it's homework go to your teacher and tell him/her "We don't get it."

Everything else is becoming pretty pointless.
Re: Sql Query (very Urgent) [message #258818 is a reply to message #258816] Mon, 13 August 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Urgent in the title is a good way to shut me up.

Regards
Michel

[Updated on: Mon, 13 August 2007 11:07]

Report message to a moderator

Re: Sql Query (very [stupid] Urgent) (merged) [message #258820 is a reply to message #258760] Mon, 13 August 2007 11:11 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
When you say "Not working in sql", what EXACTLY do you mean. What query tool are you using. your question has been correctly answered 6 times in this thread.
Re: Sql query urgent [message #258822 is a reply to message #258794] Mon, 13 August 2007 11:11 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
SQL> select name
  2  from name
  3  where instr(name, 's') > 0 
  4     or instr(name, 'v') > 0 ;

NAME
----------
sankar
veera
ramesh
ksarvesh
Re: Sql query urgent [message #258823 is a reply to message #258822] Mon, 13 August 2007 11:18 Go to previous messageGo to next message
sdvenk
Messages: 5
Registered: August 2007
Junior Member
Habeeb,
I told u that i need a single line query,
I mean that as sQL older versions
Re: Sql query urgent [message #258825 is a reply to message #258823] Mon, 13 August 2007 11:22 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
sdvenk wrote on Mon, 13 August 2007 11:18
Habeeb,
I told u that i need a single line query,
I mean that as sQL older versions


WHAT TOOL ARE YOU USING THAT HIS QUERY DOESN'T WORK. WILL YOU ANSWER THE QUESTION!!!!!!!!!! Copy the failed execution from the screen and paste it into this thread. You keep saying the same thing over and over and will not answer the question. I do not want one more repeat of this "I need a one line query" garbage.
Re: Sql query urgent [message #258851 is a reply to message #258825] Mon, 13 August 2007 11:57 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
I have no idea when you say you want to use a single line query. This is what I would do.
Create a function and use it in the query.
Code is below.

SQL> CREATE OR REPLACE FUNCTION r_name(p_name varchar2)
  2  RETURN varchar2
  3  AS
  4    v_name varchar2(10);
  5  BEGIN
  6    SELECT name
  7      INTO v_name
  8      FROM name
  9     WHERE (INSTR(name, 's') > 0 or INSTR(name, 'v') > 0)
 10       AND name = p_name;
 11   RETURN v_name;
 12  END;
 13  /

Function created.

SQL> SELECT r_name(name) 
  2  FROM name
  3  WHERE r_name(name) IS NOT NULL;

R_NAME(NAME)
---------------------------------------
sankar
veera
ramesh
ksarvesh


I hope this works...
Re: Sql query urgent [message #258868 is a reply to message #258823] Mon, 13 August 2007 12:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Creating a new user won't fool us, especially since you post from the exact same IP number.

Now sit back, take a deep breath and imagine, strange as it may sound, that we have NO CLUE as to what your project involves. We don't know what database version you use, what other tools, we don't know the purpose of your project, etc.
Just think of us as people that are, say at the other end of the world.

Now, explain to us, what it is you need. No, do not repeat the same stuff you already posted 8 times, but begin by telling us what a 'single line query' is
Then tell us who it was that told you you MUST use one
Finally open sqlplus, paste one of the six valid answers already posted, execute it and copy-paste the outcome (PLUS your inputs) here, so we can see what you did. Explain why it is not what you expect.
Re: Sql query urgent [message #258933 is a reply to message #258822] Mon, 13 August 2007 16:27 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think I got it!

This answer is close ...
Habeeb
SQL> select name
  2  from name
  3  where instr(name, 's') > 0 
  4     or instr(name, 'v') > 0 ;

NAME
----------
sankar
veera
ramesh
ksarvesh



... but here's what prevents it from being correct:
sdvenk
Habeeb,
I told u that i need a single line query,
I mean that as sQL older versions


So, here it is - a single line query:
SQL> select name from name where instr(name, 's') > 0 or instr(name, 'v') > 0;


(am I smart or what?) ./fa/1986/0/
Re: Find names containing letters (Merged) [message #258935 is a reply to message #258760] Mon, 13 August 2007 16:31 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Laughing . Pure genius!! However it would still be nice if the original poster responded.
Re: Find names containing letters (Merged) [message #259128 is a reply to message #258935] Tue, 14 August 2007 08:41 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I have mentioned to this OP many times to seek a new profession as they have no clue, can't follow rules, answer questions, or comprehend any answers given to them.
I am at a loss as to what profession would suit someone this inept.
Re: Find names containing letters (Merged) [message #259309 is a reply to message #259128] Wed, 15 August 2007 00:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
IT manager
Re: Find names containing letters (Merged) [message #259322 is a reply to message #259309] Wed, 15 August 2007 01:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing Of course!

Regards
Michel
Previous Topic: Help w/ execute immediate
Next Topic: Critical Query
Goto Forum:
  


Current Time: Sat Dec 03 01:15:38 CST 2016

Total time taken to generate the page: 0.05412 seconds