概述
1.两个字段简单相似
---两个字段简单相似
CREATE
FUNCTION
DBO.FN_Resemble (
@Cloumna
NVARCHAR
(
MAX
),
@Cloumnb
NVARCHAR
(
MAX
) )
RETURNS
FLOAT
AS
BEGIN
DECLARE
@num
FLOAT
,
@len
int
SET
@Cloumna
=
ISNULL
(
@Cloumna
,
0
)
SET
@Cloumnb
=
ISNULL
(
@Cloumnb
,
0
)
SET
@len
=
1
SET
@num
=
0
WHILE
(
LEN
(
@Cloumna
)
<>
0
AND
LEN
(
@CloumnB
)
<>
0
)
BEGIN
WHILE
(
@len
<=
LEN
(
@Cloumna
))
BEGIN
DECLARE
@a
NVARCHAR
(
4
)
SET
@a
=
''
SET
@a
=
SUBSTRING
(
@Cloumna
,
@len
,
1
)
IF
(
CHARINDEX
(
@a
,
@CloumnB
)
>
0
)
BEGIN
SET
@num
=
@num
+
1
END
SET
@len
=
@len
+
1
END
SET
@num
=
@num
*
1.0
/
LEN
(
@Cloumna
)
BREAK
END
RETURN
@num
END
----测试代码
SELECT
DBO.FN_Resemble(
'ABDC321G'
,
'ABDC123G'
)
2.两个字段顺序相似
---两个字段顺序相似
CREATE
FUNCTION
DBO.FN_Resemble_order (
@Cloumna
NVARCHAR
(
MAX
),
@Cloumnb
NVARCHAR
(
MAX
) )
RETURNS
FLOAT
AS
BEGIN
DECLARE
@num
FLOAT
,
@len
int
SET
@Cloumna
=
ISNULL
(
@Cloumna
,
0
)
SET
@Cloumnb
=
ISNULL
(
@Cloumnb
,
0
)
SET
@len
=
1
SET
@num
=
0
WHILE
(
LEN
(
@Cloumna
)
<>
0
AND
LEN
(
@CloumnB
)
<>
0
)
BEGIN
DECLARE
@a
NVARCHAR
(
4
)
DECLARE
@b
NVARCHAR
(
4
)
IF
(
LEN
(
@Cloumna
)
>=
LEN
(
@CloumnB
))
BEGIN
WHILE
(
@len
<=
LEN
(
@CloumnB
))
BEGIN
SET
@a
=
''
SET
@a
=
SUBSTRING
(
@Cloumna
,
@len
,
1
)
SET
@b
=
''
SET
@b
=
SUBSTRING
(
@CloumnB
,
@len
,
1
)
IF
(
@a
=
@b
)
BEGIN
SET
@num
=
@num
+
1
END
ELSE
BEGIN
break
END
SET
@len
=
@len
+
1
END
END
ELSE
IF
(
LEN
(
@Cloumna
)
<
LEN
(
@CloumnB
))
BEGIN
WHILE
(
@len
<=
LEN
(
@Cloumna
))
BEGIN
SET
@a
=
''
SET
@a
=
SUBSTRING
(
@Cloumna
,
@len
,
1
)
SET
@b
=
''
SET
@b
=
SUBSTRING
(
@CloumnB
,
@len
,
1
)
IF
(
@a
=
@b
)
BEGIN
SET
@num
=
@num
+
1
END
ELSE
BEGIN
break
END
SET
@len
=
@len
+
1
END
END
SET
@num
=
@num
*
1.0
/
LEN
(
@Cloumna
)
BREAK
END
RETURN
@num
END
go
----测试代码
SELECT
DBO.FN_Resemble_order(
'ABDC456G'
,
'ABDC123G'
)
3.两个字段一对一相似
---两个字段一对一相似
CREATE
FUNCTION
DBO.FN_Resemble_onebyone (
@Cloumna
NVARCHAR
(
MAX
),
@Cloumnb
NVARCHAR
(
MAX
) )
RETURNS
FLOAT
AS
BEGIN
DECLARE
@num
FLOAT
,
@len
int
SET
@Cloumna
=
ISNULL
(
@Cloumna
,
0
)
SET
@Cloumnb
=
ISNULL
(
@Cloumnb
,
0
)
SET
@len
=
1
SET
@num
=
0
WHILE
(
LEN
(
@Cloumna
)
<>
0
AND
LEN
(
@CloumnB
)
<>
0
)
BEGIN
DECLARE
@a
NVARCHAR
(
4
)
DECLARE
@b
NVARCHAR
(
4
)
IF
(
LEN
(
@Cloumna
)
>=
LEN
(
@CloumnB
))
BEGIN
WHILE
(
@len
<=
LEN
(
@CloumnB
))
BEGIN
SET
@a
=
''
SET
@a
=
SUBSTRING
(
@Cloumna
,
@len
,
1
)
SET
@b
=
''
SET
@b
=
SUBSTRING
(
@CloumnB
,
@len
,
1
)
IF
(
@a
=
@b
)
BEGIN
SET
@num
=
@num
+
1
END
SET
@len
=
@len
+
1
END
END
ELSE
IF
(
LEN
(
@Cloumna
)
<
LEN
(
@CloumnB
))
BEGIN
WHILE
(
@len
<=
LEN
(
@Cloumna
))
BEGIN
SET
@a
=
''
SET
@a
=
SUBSTRING
(
@Cloumna
,
@len
,
1
)
SET
@b
=
''
SET
@b
=
SUBSTRING
(
@CloumnB
,
@len
,
1
)
IF
(
@a
=
@b
)
BEGIN
SET
@num
=
@num
+
1
END
SET
@len
=
@len
+
1
END
END
SET
@num
=
@num
*
1.0
/
LEN
(
@Cloumna
)
BREAK
END
RETURN
@num
END
----测试代码
SELECT
DBO.FN_Resemble_onebyone(
'ABDC456G'
,
'ABDC123G'
)
两个字段相似
IF object_id('F_strcompare') IS NOT NULL
BEGIN
PRINT 'Dropping function F_strcompare'
DROP FUNCTION F_strcompare
IF @@ERROR = 0 PRINT 'Function F_strcompare dropped '
END
go
CREATE FUNCTION F_strcompare(@str1 VARCHAR(8000),
@str2 VARCHAR(8000))
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @i INT,
@j INT,
@k INT,
@ret VARCHAR(10)
SELECT @i = MAX(strlen),
@j = MIN(strlen),
@k = 0
FROM (SELECT strlen=Len(@str1)
UNION
SELECT strlen=Len(@str2)) t
IF( @j = 0 )
RETURN '0%'
WHILE @j > 0
BEGIN
IF Substring(@str1, @j, 1) = Substring(@str2, @j, 1)
SET @k=@k + 1
SET @j=@j - 1
END
SET @ret = Rtrim(CAST(@k * 100.0 / @i AS NUMERIC(3, 0))) + '%'
RETURN @ret
END
GO
最后
以上就是清爽西装为你收集整理的SQLsever 两个字段相似程度算法 （总结）的全部内容，希望文章能够帮你解决SQLsever 两个字段相似程度算法 （总结）所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复