查找数据库中字符串相同位置不同字符个数小于5的记录
use tmss
go
create table test
(id varchar(50),pic varchar(50), hashcode varchar(16))
insert into test values('1', '2012120910403250c3fa209bf48.jpg', 'bf8f83818080c0f1')
insert into test values('2', '2012120620430750c092db26557.jpg', 'ff9880f0f680ceff')
insert into test values('3', '2012120619582550c08861eb062.jpg', '7f7f004f7f7f7c7f')
insert into test values('4', '2012112911072650b6d16e7f21f.jpg', '7f7f004f7f7f007f')
go
create table test
(id varchar(50),pic varchar(50), hashcode varchar(16))
insert into test values('1', '2012120910403250c3fa209bf48.jpg', 'bf8f83818080c0f1')
insert into test values('2', '2012120620430750c092db26557.jpg', 'ff9880f0f680ceff')
insert into test values('3', '2012120619582550c08861eb062.jpg', '7f7f004f7f7f7c7f')
insert into test values('4', '2012112911072650b6d16e7f21f.jpg', '7f7f004f7f7f007f')
GO
create FUNCTION f_test(
@input VARCHAR(50),
@value VARCHAR(50)
)
RETURNS INT
AS
BEGIN
RETURN (SELECT sum(CASE WHEN SUBSTRING(@input,number,1)=SUBSTRING(@value,number,1) THEN 0 ELSE 1 END)
FROM [master].dbo.spt_values sv WHERE sv.[type]='p' AND LEN(@input)>=number AND sv.number>0 )
END
DECLARE @input VARCHAR(50)
SET @input='7f7f004f7f7f00af'
SELECT * FROM test t WHERE dbo.f_test(@input,hashcode)<=5
/*
id pic hashcode
-------------------------------------------------- -------------------------------------------------- ----------------
3 2012120619582550c08861eb062.jpg 7f7f004f7f7f7c7f
4 2012112911072650b6d16e7f21f.jpg 7f7f004f7f7f007f
(2 行受影响)
*/
DROP TABLE test
DROP FUNCTION f_test
