请教在sql中实现同音及相似查询

chjpeng 2005-06-27 10:46:52

搜索了很多文章/贴子，发现类似问题一直没有得到解答，特发贴请教：

现在已经有了取得字符串首字母的函数，
但是取得字符串全拼的函数只有oracle的，sql里面不知道怎么实现？

同音查询
select f1,f2,f3 from tb where fc like '%@fc%' or hz2py(fc) like '%'+hz2py(@fc)+'%'

fc字段是关键字所在字段，如果整列用函数转换，查询效率怎么控制？

相似查询的情况就更不明白了...

...全文

491 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

天地客人 2005-07-13

打赏
举报

帮你UP

jixiaojie 2005-07-13

打赏
举报

hglhyy 2005-07-12

打赏
举报

关注ing...

chjpeng 2005-07-12

打赏
举报

谢谢，不过soundex是针对英文发音近似或同音的，汉字没有啊~~

bugchen888 2005-07-11

打赏
举报

SOUNDEX
返回由四个字符组成的代码 (SOUNDEX) 以评估两个字符串的相似性。

语法
SOUNDEX ( character_expression )

参数
character_expression

是字符数据的字母数字表达式。character_expression 可以是常数、变量或列。

返回类型
char

注释
SOUNDEX 将 alpha 字符串转换成由四个字符组成的代码，以查找相似的词或名称。代码的第一个字符是 character_expression 的第一个字符，代码的第二个字符到第四个字符是数字。将忽略 character_expression 中的元音，除非它们是字符串的第一个字母。可以嵌套字符串函数。

示例
下例显示 SOUNDEX 函数及相关的 DIFFERENCE 函数。在第一个示例中，返回所有辅音字母的标准 SOUNDEX 值。为 Smith 和 Smythe 返回的 SOUNDEX 结果相同，因为不包括所有元音、字母 y、连写字母和字母 h。

-- Using SOUNDEX
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')

下面是结果集：

----- -----
S530 S530

(1 row(s) affected)

DIFFERENCE 函数比较 SOUNDEX 模式结果的差。第一个示例显示两个仅元音不同的字符串。返回的差是 4（可能的最小差）。

-- Using DIFFERENCE
SELECT DIFFERENCE('Smithers', 'Smythers')
GO

下面是结果集：

-----------
4

(1 row(s) affected)

在下例中，字符串的辅音不同，所以返回的差是 2（较高的差）。

SELECT DIFFERENCE('Anothers', 'Brothers')
GO

下面是结果集：

-----------
2

(1 row(s) affected)

天地客人 2005-07-07

打赏
举报

帮UP，关注中！

辉说慧语 2005-07-07

打赏
举报

mark

chjpeng 2005-07-05

打赏
举报

谢谢，取首字母的早就有啦~~
就差全拼和优化的技巧了~~:)

超级大笨狼 2005-07-05

打赏
举报

/*-1.-获得汉字字符串的首字母

根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetPy]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetPy]
GO

--创建取拼音函数
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)

select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '嘸'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
else set @c='a'
set @return=@return+@c
end
return(@return)
end

go
--测试
select dbo.fgetpy('青藏高原') as 东莞市,dbo.fgetpy('ab中c国人') as 中国人

--删除拼音函数
drop function fgetpy

echoxue 2005-07-01

打赏
举报

在ＳＱＬ中有关键字的查询

某字段 LIKE [a,A]

就是一个查表的方式,把一些相关的同音放入表中
,然后匹配

VB.NET
的代码
''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary>Convert 濁音 to ArrayList
''' <param>ByRef arrayList As ArrayList, ByVal position As Integer</param>
''' <returns></returns>
Private Shared Sub ConvertToReturnArray(ByRef arrayList As ArrayList, ByVal position As Integer)

Dim i%
For i = position To arrayList.Count - 1
If hashTable_O.ContainsKey(arrayList.Item(i)) Then
' get new Arraylist
Dim relatedArray As arrayList = GetNewArrayList(arrayList)
' It change the value according to Key(Value) of HashTable
relatedArray.Item(i) = hashTable_O.Item(relatedArray.Item(i))
' add to final ArrayList
AddReturnString(relatedArray)
' The loop call for new
ConvertToReturnArray(arrayList, i + 1)
' The loop call for origion ArrayList
ConvertToReturnArray(relatedArray, i + 1)
Exit For
End If
Next

End Sub

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary>It Get sequential Sql Condition and Add to final ArrayList
''' <param>ByVal stringArrayList As ArrayList</param>
''' <returns></returns>
Private Shared Sub AddReturnString(ByVal stringArrayList As ArrayList)

Dim strSQlCondition As String = ""
Dim i%
For i = 0 To stringArrayList.Count - 1
strSQlCondition += stringArrayList.Item(i).ToString
Next
returnList.Add(strSQlCondition)

End Sub

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary> It will colne a New arrayList from origion ArrayList
''' <param>ByRef sourceArrayList As ArrayList</param>
''' <returns></returns>
Private Shared Function GetNewArrayList(ByRef sourceArrayList As ArrayList) As ArrayList

Dim newArrayList As ArrayList = New ArrayList
Dim i%
For i = 0 To sourceArrayList.Count - 1
newArrayList.Add(sourceArrayList.Item(i).ToString)
Next
Return newArrayList

End Function

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary> Check the char whether exist hashTable
''' </summary>if exist will retun char of HashTable,else return origion char
''' <param>ByVal str As Strin</param>
''' <returns></returns>
Private Shared Function GetReplaceStr(ByVal str As String) As String

If hashTable_.ContainsKey(str) Then
Return hashTable_.Item(str).ToString
End If
Return str

End Function

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary> It will replace the char with fining char of hashTable
''' <param>ByRef arrayList As ArrayList</param>
''' <returns></returns>
Private Shared Sub ReplaceWithHashTable(ByRef arrayList As ArrayList)

Dim i%
For i = 0 To arrayList.Count - 1
arrayList.Item(i) = GetReplaceStr(arrayList.Item(i).ToString)
Next

End Sub

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary> It split source String and then add into arrayList
''' <param>ByVal strGetValue As String</param>
''' <returns></returns>
Private Shared Function ConvertToArrayList(ByVal strGetValue As String) As ArrayList

Dim arrayList As arrayList = New arrayList
Dim i%
Dim iLen%
For i = 0 To strGetValue.Length - 1
iLen = 2
If i + 2 > strGetValue.Length Then ' Checking the length whether it is beyond the boundary
iLen = strGetValue.Length - i
End If

If CheckStringState(strGetValue.Substring(i, iLen)) Then ' Get two char
arrayList.Add(strGetValue.Substring(i, iLen))
i = i + 1
Else
arrayList.Add(strGetValue.Substring(i, 1)) ' Get one Char
End If
Next

Return arrayList

End Function

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary> Check the key whether it exist in HashTable,
''' </summary> it Will return true if it exist in,else return false
''' <param>ByVal strKey As String</param>
''' <returns></returns>
Private Shared Function CheckStringState(ByVal strKey As String) As Boolean

If hashTable_.ContainsKey(strKey) Or hashTable_O.ContainsKey(strKey) Then
Return True
End If
Return False

End Function

''' <summary>
''' 日付　　：2005/06/13　　
''' 作成者　：薛　　　　
''' </summary> 拗音,促音,濁音 Init
''' <param>ByVal strGetValue As String</param>
''' <returns></returns>
Public Shared Function GetSqlConditionArrayList(ByVal strGetValue As String) As ArrayList

returnList.Clear()

' Set a ArrayList that can save the changing String
Dim strAfterAplitArray As ArrayList = New ArrayList
hashTable_.Clear()
hashTable_O.Clear()

' 拗音
'hashTable_.Add("ャ", "[ヤ,ャ]")
'hashTable_.Add("ヤ", "[ヤ,ャ]")

hashTable_.Add("ｬ", "[ｬ,ﾔ]")
hashTable_.Add("ﾔ", "[ｬ,ﾔ]")

'hashTable_.Add("ュ", "[ユ,ュ]")
'hashTable_.Add("ユ", "[ユ,ュ]")

hashTable_.Add("ｭ", "[ｭ,ﾕ]")
hashTable_.Add("ﾕ", "[ｭ,ﾕ]")

'hashTable_.Add("ョ", "[ヨ,ョ]")
'hashTable_.Add("ヨ", "[ヨ,ョ]")

hashTable_.Add("ﾖ", "[ﾖ,ｮ]")
hashTable_.Add("ｮ", "[ﾖ,ｮ]")

'hashTable_.Add("ッ", "[ツ,ッ]")
'hashTable_.Add("ツ", "[ツ,ッ]")

hashTable_.Add("ｯ", "[ｯ,ﾂ]")
hashTable_.Add("ﾂ", "[ｯ,ﾂ]")

'hashTable_.Add("ジ", "[ヂ,ジ]")
'hashTable_.Add("ヂ", "[ヂ,ジ]")

hashTable_.Add("ｼﾞ", "[ｼﾞ,ﾁﾞ]")
hashTable_.Add("ﾁﾞ", "[ｼﾞ,ﾁﾞ]")

' 促音
'hashTable_.Add("ズ", "[ヅ,ズ]")
'hashTable_.Add("ヅ", "[ヅ,ズ]")

hashTable_.Add("ｽﾞ", "[ｽﾞ,ﾂﾞ]")
hashTable_.Add("ﾂﾞ", "[ｽﾞ,ﾂﾞ]")

' 濁音
'hashTable_O.Add("ヴァ", "バ")
'hashTable_O.Add("バ", "ヴァ")

hashTable_O.Add("ｳﾞｧ", "ﾊﾞ")
hashTable_O.Add("ﾊﾞ", "ｳﾞｧ")

'hashTable_O.Add("ベ", "ヴェ")
'hashTable_O.Add("ヴェ", "ベ")

hashTable_O.Add("ﾍﾞ", "ｳﾞｪ")
hashTable_O.Add("ｳﾞｪ", "ﾍﾞ")

' It split source value of String and then add into ArrayList
strAfterAplitArray = ConvertToArrayList(strGetValue)

' 拗音,促音 replace according to changing rule
ReplaceWithHashTable(strAfterAplitArray)

' 濁音 replace according to changing rule
ConvertToReturnArray(strAfterAplitArray, 0)
'
AddReturnString(strAfterAplitArray)

Return returnList

End Function

chjpeng 2005-07-01