search - ignore punctuation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

search - ignore punctuation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-24 : 08:45:15

david writes "Searching a number of varchar columns that contain titles.

Currently using

select *
from currentclcqueue
where clientname like (%ABC%)

But user is complaining that the search sometime fails because of punctuation marks in the text we are searching on.

They type in ABC expecting to find ABC plus also A.B.C
Don't have any control over what is entered into the table nor do I have the rights to make changes to the table. Like an extra column containing a copy of the search field with the punctuation marks already removed.

I know this works:

select *
from currentclcqueue
where replace(clientname,'.','') like '%ABC%'

Is there an easier way? This won't handle other punctuation marks like an '-' which is also in the text. Also is there much of an impact on the speed of the search using an REPLACE commend?

Currently not able to use full text indexing.

thanks
david"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-24 : 08:51:18

using any function on the column makes a table scan instead of index seek or scan. it can't use indexes on the column.
i don't think there's much you can do without changing anything.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-24 : 09:10:56

declare @t table(no numeric,nn varchar(100),vv varchar(10))
insert into @t values(123,'abc','234lk')
insert into @t values(123,'lkjabcerf','9823')
insert into @t values(123,'a.b.c','kjdg')
insert into @t values(2733,'kjsdfgdf','lkjfd')
insert into @t values(265,'kjshfgdf','lkijsd')
Declare @s1 nvarchar(50)
Declare @s2 nvarchar(50)
Declare @i int

set @s1='abc'
set @s2=''
Set @i=1
print @s1

while @i<=len(@s1)
begin
Set @s2=@s2 + substring(@s1,@i,1) +'.'
Set @i=@i+1
end
print @s2
Select * from @t where (nn like '%'+@s1+'%' or nn like '%' + left(@s2,len(@s2)-1) +'%')

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources