两个表的模糊匹配问题！(100分)

[]有两个表，zhi为一个表的字段（只有一个 记录），zhi2为另一个表的字段（有很多
个记录），zhi的记录为“大富翁论坛的高手很多”，zhi2的记录包括“大富翁论坛，
DELPHI，高手，人气。。。。”，问有什么方法可以把“大富翁论坛”和“高手”这两个
关键字提取出来。或者有没有类似这种功能的
命令呢：select * from 表1 where zhi like:%zhi2%？（zhi2代表字段）

假设zhi和zhi2的字段都是name:

select zhi2.* from zhi2,zhi where charindex(zhi2.name,zhi.name) >0

select zhi2.大富翁论坛,zhi2.高手 from zhi1,zhi2
where zhi2.field1 += zhi1.field1

用类似于：strpos()的函数

这个功能用SQL语句不知道呢//学习.
如果是程序的话,就用循环自己判断,

to bluerain：“假设zhi和zhi2的字段都是name”是什么意思？

to f950436：不能这样，因为我不是只是为了提取“大富翁论坛”和“高手”这两个
关键字，也就是说zhi的记录是可变的，那么要提取的关键字就不同。

to hbezwwl:“用类似于：strpos()的函数”，能详细说一下吗？

to yxdelphi：不能用循环语句，因为zhi2的记录数上万条，查询的速度会比较慢。

我的意思是假设你存放zhi的记录为“大富翁论坛的高手很多”的字段名叫name，
zhi2的记录包括“大富翁论坛，DELPHI，高手，人气。。。。”，的字段名也叫name[]

charindex(zhi2.name,zhi.name), charindex函数是查询zhi2.name是否是zhi.name的
子字符串. 如果是则返回值 > 0.

DB2:
>>-POSSTR--(--source-string--,--search-string--)---------------><


The schema is SYSIBM.

The POSSTR function returns the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string). Numbers for the search-string position start at 1 (not 0).

The result of the function is a large integer. If either of the arguments can be null, the result can be null; if either of the arguments is null, the result is the null value.


source-string
An expression that specifies the source string in which the search is to take place.
The expression can be specified by any one of:

a constant
a special register
a host variable (including a locator variable or a file reference variable)
a scalar function
a large object locator
a column name
an expression concatenating any of the above

search-string
An expression that specifies the string that is to be searched for.

The expression can be specified by any one of:

a constant
a special register
a host variable
a scalar function whose operands are any of the above
an expression concatenating any of the above
with the restrictions that:

No element in the expression can be of type LONG VARCHAR, CLOB, LONG VARGRAPHIC or DBCLOB. In addition, it cannot be a BLOB file reference variable.
The actual length of search-string cannot be more than 4 000 bytes.
Note that these rules are the same as those for the pattern-expression described in LIKE Predicate.

Both search-string and source-string have zero or more contiguous positions. If the strings are character or binary strings, a position is a byte. If the strings are graphic strings, a position is a graphic (DBCS) character.

The POSSTR function accepts mixed data strings. However, POSSTR operates on a strict byte-count basis, oblivious to changes between single and multi-byte characters.

The following rules apply:

The data types of source-string and search-string must be compatible, otherwise an error is raised (SQLSTATE 42884).
If source-string is a character string, then search-string must be a character string, but not a CLOB or LONG VARCHAR, with an actual length of 32 672 bytes or less.
If source-string is a graphic string, then search-string must be a graphic string, but not a DBCLOB or LONG VARGRAPHIC, with an actual length of 16 336 double-byte characters or less.
If source-string is a binary string, then search-string must be a binary string with an actual length of 32 672 bytes or less.
If search-string has a length of zero, the result returned by the function is 1.
Otherwise:
If source-string has a length of zero, the result returned by the function is zero.
Otherwise:
If the value of search-string is equal to an identical length substring of contiguous positions from the value of source-string, then the result returned by the function is the starting position of the first such substring within the source-string value.
Otherwise, the result returned by the function is 0.
Example

Select RECEIVED and SUBJECT columns as well as the starting position of the words 'GOOD BEER' within the NOTE_TEXT column for all entries in the IN_TRAY table that contain these words.
SELECT RECEIVED, SUBJECT, POSSTR(NOTE_TEXT, 'GOOD BEER')
FROM IN_TRAY
WHERE POSSTR(NOTE_TEXT, 'GOOD BEER') <> 0

to bluerain:用了你所说的命令，但总是提示“capability not surpported”；
我的程序如下：with query1 do
begin
close;
with sql do
begin
clear;
add('select gjz.* from gjz,wenti where charindex(gjz.gjc,wenti.wenti) >0');
open;
end;
end;
gjz和wenti为表

忘了说了,你用的是什么数据库系统.
在sql server7.5下,我试验过了,应该是没有问题的.
如果是别的数据库系统,charindex函数就不支持了.

说清楚你用的什么数据库？

sql2000

你可以先在sql的query analyse中运行一下,看看是什么问题,
charindex是查找一个字符串中子字符串的位置.你在sql2000的
online help中查查有没有这个函数.

你能不能把gjz和weiti这两个表贴一下,包括记录(可以取几条),这样大家也可以
帮你测试了.

表：(gjz) 字段 gjz 表：(wenti) 字段 wenti
记录 操作系统 记录 操作系统的定义是什么？
定义 （只有一个记录）
性质
。。。
现在没有那个“capability not surpported”的问题了，可是远行之后没有找到反应。
程序如下：begin
with adoquery1 do
begin
close;
with sql do
begin
clear;
add('select gjz.* from gjz,wenti where charindex(gjz.gjz,wenti.wenti)> 0');
open;
adoquery1.First;
if not adoquery1.Eof then showmessage('asdf');
end;
end;
end;

你的问题可真是奇怪,我把你的记录导入到数据库中,然后
with adoquery1 do
begin
with adoquery1 do
begin
close;
sql.clear;
sql.add('select gjz.* from gjz , wenti where charindex(gjz.gjz,wenti.wenti) > 0');
open;
end;
end;
把返回的记录显示在dbgrid中,是两条记录:
gjz
----------------------------------------------------------------------------------------------------
操作系统
定义

我用的是sql server7.5,你的虽然是sql 2000,但是没有理由相差怎么大.
最有可能出问题的地方我想要么是charindex函数的定义,你在sql2000的帮助中
看一下charindex的具体定义; 第二个可能出问题的地方就是你建表的地方,表名
和字段名是相同的,这不是好习惯,改一下试试看.
我没有装sql2000,自己摸索一下吧.

to bluerain:我在sql2000的帮助中看过charindex的具体定义了，程序应该是这样写
的；你说的坏习惯也改了。可是还是没有记录返回。而且退出的时候提示“bof或eof中
有一个是“真”或者当前的记录已被删除，所需的操作要求一个当前的记录”。

天哪.原来你没有打ado的补丁,你用的是D5吧.
查一下这方面的帖子,都讨论滥了.

没那么麻烦，这样应该就可以了。
select * from 表1 a,表2 b
where a.zhi like '%'+ltrim(rtrim(b.zhi2))+'%'

to bluerain:在delphi6下也不行的,没有记录返回.

你在sql2000的query analyzer下执行sql语句,看看对不对.
query analyzer应该用过吧,不然我真要晕了.
如果在Query analyzer下可以,肯定就是Delphi的问题了.