三位斑竹,各位高手帮忙：关于一个模糊查询的优化解

[yokel]

如一表有id (PK)|Col1|Col2 三列，查询针对col1，拥有数据约50万行
当输入字符串“I am a boy”时，能返回最相似的行，如：
I am a boy --（0差异）
I an a boy --（1个字母差异）
I am A Boy
Iam a boy --（少一个空格）
I am a boy --（多一个空格）
而“I Am A boy”的行有两初不同，不返回
我现在的办法是使用一个很长带like条件的select语句，基本可以实现，
但每次查询需要1min左右，对col1加上index也无多大改进。考虑到长度
特点——返回col1的长度必为len(strInput)±1范围，正在做相关优化，
但实在没有多大信心，望各位高手赐予援手，分不够可以再加。
在线焦急等待中

[titanicliu]

DECLARE @strInput varchar(200)
SET strInput='strInput'
SELECT COL1 FROM yourTable
WHERE LEN(COL1) BETWEEN LEN(@strInput)-1 AND LEN(@strInput)+1
AND COL1 LIKE REPLACE(@strInput,' ','%')

[CrazyFor]

--------------------

declare
@inputstr varchar(1000),
@str varchar(8000),
@allstr varchar(8000),
@tmpstr varchar(150),
@i INT,
@J INT,
@SI int,
@SJ int

set @inputStr='I am a boy'
set @tmpstr='abcdefghijklmnopqrstuvwxyZABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890-=~!@#$%^&*()_+|[]{};:",./<>?'''

SELECT @J=LEN( @TMPSTR),@SJ=len(@inputStr),@I=1,@SI=1

set @allstr=''''+@inputStr+''''

---------------相差一个字符
WHILE @SI<+@SJ
begin
set @str=substring(@inputStr,@SI,1)

if ascII(@str)=32 ---长度加减只针对空格的情况
begin
set @allstr=@allstr+','''+left(@inputStr,@SI-1)+right(@inputStr,@SJ-@SI)+''''
set @allstr=@allstr+','''+left(@inputStr,@SI-1)+' '+right(@inputStr,@SJ-@SI)+''''
end
while @I<=@J
begin
select @allstr=@allstr+','''+left(@inputStr,@SI-1)+substring(@tmpstr,@I,1) +right(@inputStr,@SJ-@SI)+''''
set @I=@I+1

end
set @SI=@SI+1
end
print @allstr

---------------------------得到下面的字符串，这样应该可以实现
'I am a boy','a am a boy','b am a boy','c am a boy','d am a boy','e am a boy','f am a boy','g am a boy','h am a boy','i am a boy','j am a boy','k am a boy','l am a boy','m am a boy','n am a boy','o am a boy','p am a boy','q am a boy','r am a boy','s am a boy','t am a boy','u am a boy','v am a boy','w am a boy','x am a boy','y am a boy','Z am a boy','A am a boy','B am a boy','C am a boy','D am a boy','E am a boy','F am a boy','G am a boy','H am a boy','I am a boy','J am a boy','K am a boy','L am a boy','M am a boy','N am a boy','O am a boy','P am a boy','Q am a boy','R am a boy','S am a boy','T am a boy','U am a boy','V am a boy','W am a boy','X am a boy','Y am a boy','Z am a boy','1 am a boy','2 am a boy','3 am a boy','4 am a boy','5 am a boy','6 am a boy','7 am a boy','8 am a boy','9 am a boy','0 am a boy','- am a boy','= am a boy','~ am a boy','! am a boy','@ am a boy','# am a boy','$ am a boy','% am a boy','^ am a boy','& am a boy','* am a boy','( am a boy',') am a boy','_ am a boy','+ am a boy','| am a boy','[ am a boy','] am a boy','{ am a boy','} am a boy','; am a boy',': am a boy','" am a boy',', am a boy','. am a boy','/ am a boy','< am a boy','> am a boy','? am a boy','' am a boy','Iam a boy','I am a boy','I ama boy','I am a boy','I am aboy','I am a boy'

[CrazyFor]

更新，最好学是填入临时表，好象8000个字符会不够长。：）
--------------------

declare
@inputstr varchar(1000),
@str varchar(8000),
@allstr varchar(8000),
@tmpstr varchar(150),
@i INT,
@J INT,
@SI int,
@SJ int

set @inputStr='I am a boy'
set @tmpstr='abcdefghijklmnopqrstuvwxyZABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890-=~!@#$%^&*()_+|[]{};:",./<>?'''

SELECT @J=LEN( @TMPSTR),@SJ=len(@inputStr),@I=1,@SI=1

set @allstr=''''+@inputStr+''''

---------------相差一个字符
WHILE @SI<+@SJ
begin
set @str=substring(@inputStr,@SI,1)

if ascII(@str)=32 ---长度加减只针对空格的情况
begin
set @allstr=@allstr+','''+left(@inputStr,@SI-1)+right(@inputStr,@SJ-@SI)+''''
set @allstr=@allstr+','''+left(@inputStr,@SI-1)+' '+right(@inputStr,@SJ-@SI)+''''
end
set @I=1
while @I<=@J
begin
select @allstr=@allstr+','''+left(@inputStr,@SI-1)+substring(@tmpstr,@I,1) +right(@inputStr,@SJ-@SI)+''''
set @I=@I+1

end
set @SI=@SI+1
end
print @allstr

[CrazyFor]

-----插入表变量的例子。
--------------------
declare @temptable table (string varchar(800))
declare
@inputstr varchar(1000),
@str varchar(8000),
@allstr varchar(8000),
@tmpstr varchar(150),
@i INT,
@J INT,
@SI int,
@SJ int



set @inputStr='I am a boy'
set @tmpstr='abcdefghijklmnopqrstuvwxyZABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890-=~!@#$%^&*()_+|[]{};:",./<>?'''

SELECT @J=LEN( @TMPSTR),@SJ=len(@inputStr),@I=1,@SI=1

---set @allstr=''''+@inputStr+''''
insert into @temptable (string)values(@inputStr)

---------------相差一个字符
WHILE @SI<+@SJ
begin
set @str=substring(@inputStr,@SI,1)

if ascII(@str)=32 ---长度加减只针对空格的情况
begin
/*
set @allstr=@allstr+','''+left(@inputStr,@SI-1)+right(@inputStr,@SJ-@SI)+''''
set @allstr=@allstr+','''+left(@inputStr,@SI-1)+' '+right(@inputStr,@SJ-@SI)+''''
*/
insert into @temptable (string)values(left(@inputStr,@SI-1)+right(@inputStr,@SJ-@SI))
insert into @temptable (string)values(left(@inputStr,@SI-1)+' '+right(@inputStr,@SJ-@SI))
end
set @I=1
while @I<=@J
begin
--select @allstr=@allstr+','''+left(@inputStr,@SI-1)+substring(@tmpstr,@I,1) +right(@inputStr,@SJ-@SI)+''''
insert into @temptable (string)values(left(@inputStr,@SI-1)+substring(@tmpstr,@I,1)+right(@inputStr,@SJ-@SI))
set @I=@I+1

end
set @SI=@SI+1
end
select * from @temptable