Home » SQL & PL/SQL » SQL & PL/SQL » Complex SQL Statement - Comparing Strings (Oracle 9iR2)
Complex SQL Statement - Comparing Strings [message #376476] Wed, 17 December 2008 08:31 Go to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hi,

I have a really complicated issue. The starting position is the following:

Table_1 (1te row = Header row):

ID________Name
1_________Hallo
2_________Tallo
3_________Huht
4_________Huhuhaha
5_________Klzauft


Table_2 (1te row = Header row):

ID________Name
1_________Hallodu
2_________ZZZZZZallo
3_________sssHuhu
4_________sssHullhaha
5_________Klaussss


I need now the following, and I think it is not that easy


Query to be billed:

The query should show me all (distinct) records from Table_1 where 4 successive characters of the namefield
(no matter from which position onwards, but from left to right) are as well in a line in the Namefield of Table_2 (again successive) at any position.
Additionally I want to show as well the 4 successive characters "Searchstring" in a seperate column.

So from the above tables, the example output of my query should look like:

Name______Suchstring
Hallo_____Hall
Hallo_____allo
Tallo_____allo
Huhuhaha__Huhu

So I hope you understand what I am looking for.

Thanks in advance for your help!
Re: Complex SQL Statement - Comparing Strings [message #376477 is a reply to message #376476] Wed, 17 December 2008 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 26 September 2007 08:20
Why you still don't want to follow the rules?

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel


and so on and so on

[Updated on: Wed, 17 December 2008 08:33]

Report message to a moderator

Re: Complex SQL Statement - Comparing Strings [message #376479 is a reply to message #376476] Wed, 17 December 2008 08:53 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
is it really complex as you claim. i doubt it and feel you yourself can solve this issue. i make this comment based on your earlier querry.
yours
dr.s.raghunathan
Re: Complex SQL Statement - Comparing Strings [message #376485 is a reply to message #376476] Wed, 17 December 2008 09:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to use a Row Generator to produce a row of data for each string of 4 consecutive characters in Table_1 and another for Table_2.

Then compare the 4 chr strings you get and see which rows match.
Re: Complex SQL Statement - Comparing Strings [message #376508 is a reply to message #376485] Wed, 17 December 2008 11:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No need for a RowGenerator for table 2 as you can use instr there, using the generated 4-letter substring from table1
Re: Complex SQL Statement - Comparing Strings [message #376511 is a reply to message #376508] Wed, 17 December 2008 11:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very good point.
Previous Topic: sql problem (merged)
Next Topic: automatic carriage return/line feed
Goto Forum:
  


Current Time: Sun Dec 11 00:20:30 CST 2016

Total time taken to generate the page: 0.09190 seconds