?
Solved

like match on 5 chars either side

Posted on 2011-05-11
22
Medium Priority
?
333 Views
Last Modified: 2012-05-11
I need to match on five characters regarless where they are at in the string.
the code should be something like this but 5 chars on either side
select a.legal_name, b.dba_name
from table_1 as a
inner join table_2 as b
on '%'+a.legal_name+'%'  like  '%'+b.dba_name+'%'

I want to take a string of five chars in the a.legal_name and match it to any five characters in the dba name

legal name - North Sunrise Store
dba name - Northwest Sunrise Store

I want to take the string of 5 chars anywhere in legal name and dba and match it
so I should be able to pull this multiple times with the 5 char strings matching anywhere
should match on North, sunri, store, etc.
0
Comment
Question by:elucero
  • 11
  • 5
  • 2
  • +2
21 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35739194
create a function

Like5Char(@str1, @str2) returns int
begin
  for i=1 to len(@str1)-5
    set result = Instr(@str2, substr(@str1,i,5))>0
    if result>0 return result
 next
  return 0;
end;

then use this function in your query

select a.legal_name, b.dba_name
from table_1 as a
inner join table_2 as b
where Like5Char(a.legal_name, b.dba_name) >0
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35739322
here is tested version
alter function LikeNChar(@str1 varchar(max), @str2 varchar(max), @n int =  3) returns int
as
begin
  declare @result int = 0;
  declare @i int = 0;

  while @i<len(@str1)-@n
  begin
    set @result = CharIndex(SubString(@str1,@i,@n), @str2); 
    if @result > 0  return @result;
    set @i=@i+1;
  end;

  return @result;
end;

select dbo.LikeNChar('This is a test string', 'I am testing this', 5)

14

Open in new window

0
 

Author Comment

by:elucero
ID: 35740310
thank you, but how do I use it in the where clause

like this will not work
select a.legal_name, b.dba_name
from table_1 as a
inner join table_2 as b
where Like5Char(a.legal_name, b.dba_name) >0
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:HainKurt
ID: 35740335
like this:

select a.legal_name, b.dba_name
from table_1 as a
inner join table_2 as b
where dbo.Like5Char(a.legal_name, b.dba_name,5) >0
0
 

Author Comment

by:elucero
ID: 35740595
thanks, it doesnt work I get this error

Incorrect syntax near the keyword 'where'

I think its looking for an on to join a and b
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35740622
replace where to on. (no points please as the credit goes to HainKurt)
select a.legal_name, b.dba_name 
from table_1 as a 
inner join table_2 as b 
on dbo.Like5Char(a.legal_name, b.dba_name,5) >0

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35740627
you have to join tables

select a.legal_name, b.dba_name
from table_1 as a
inner join table_2 as b on dbo.Like5Char(a.legal_name, b.dba_name,5) >0
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35740637
:)
0
 

Author Comment

by:elucero
ID: 35741080
yes, I got it thanks
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35741399
Objection... I posted the full function and the query (with a syntax error, and corrected it at the same time with Sharath_123, and he said " (no points please as the credit goes to HainKurt)") and I got nothing :) Please correct this...

Thanks...
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35741724
Agree with HainKurt.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35745819
where CHARINDEX(ProductName,'Ch',1) > 0
0
 
LVL 32

Expert Comment

by:awking00
ID: 35755487
I believe you may have to include an equals condition in your function. For example, in the case you select 5 characters and the legal name is 'abc' and the dba name is 'abc' I think the function as written will return 0. Sorry I don't have SQL Server to test with.
0
 

Author Comment

by:elucero
ID: 35755698
I gave the points to Hainkurt instead of  Sharath_123,  which I originally meant to do.  It was a mistake on my part.  Thanks everyone.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35755884
I guess you are right :) so fixed version will be

and in query i should be used like

dbo.LikeNChar(col1, col2, n) > 0
create function LikeNChar(@str1 varchar(max), @str2 varchar(max), @n int =  3) returns int
as
begin
  declare @result int = -1;
  declare @i int = 0;

  while @i<len(@str1)-@n
  begin
    set @result = CharIndex(SubString(@str1,@i,@n), @str2); 
    if @result >= 0  return @result;
    set @i=@i+1;
  end;

  return @result;
end;

select dbo.LikeNChar('This is a test string', 'I am testing this!', 4)
14
select dbo.LikeNChar('test', 'test', 4)
1
select dbo.LikeNChar('that', 'test another', 4)
0

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35755912
and line 4 first initialize does not mean anything :) it should be 0

function will return

0 : they are not alike
x, x>0 : yes they do alike, and position is x
create function LikeNChar(@str1 varchar(max), @str2 varchar(max), @n int =  3) returns int
as
begin
  declare @result int = 0;
  declare @i int = 0;

  while @i<len(@str1)-@n
  begin
    set @result = CharIndex(SubString(@str1,@i,@n), @str2); 
    if @result >= 0  return @result;
    set @i=@i+1;
  end;

  return @result;
end;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 35756331
The question I was having is what does the following return?
select dbo.LikeNChar('test', 'test', 5)
0
 

Author Comment

by:elucero
ID: 35756874
no, you can't this will return 0

select dbo.LikeNChar(a.legal_name, b.DBA_Name_Txt,5)
from table_1 as a, table_2 as b

this is how I used it and it works

select a.legal_name, b.DBA_Name_Txt
from table_1 as a, table_2 as b
where dbo.LikeNChar(a.legal_name, b.DBA_Name_Txt,5) >0

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35756956
:)  with the latest (or maybe it is same with the firs post :)


select dbo.LikeNChar('test', 'test', 2)
1
select dbo.LikeNChar('test', 'test', 4)
1
select dbo.LikeNChar('test', 'test', 5)
-3 ???? oops, what is this?

here is fixed version

select dbo.LikeNChar('test', 'test', 5)
1
alter function LikeNChar(@str1 varchar(max), @str2 varchar(max), @n int =  3) returns int
as
begin
  declare @result int;
  declare @i int = 1;

  if @n>len(@str1) set @n = len(@str1);
  
  while @i<=len(@str1)-@n+1
  begin
    set @result = CharIndex(SubString(@str1,@i,@n), @str2); 
    if @result > 0  return @result;
    set @i=@i+1;
  end;

  return @result;
end;

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35756961
actually, if we return 0 on line 16, it would be better :)
create function LikeNChar(@str1 varchar(max), @str2 varchar(max), @n int =  3) returns int
as
begin
  declare @result int;
  declare @i int = 1;

  if @n>len(@str1) set @n = len(@str1);
  
  while @i<=len(@str1)-@n+1
  begin
    set @result = CharIndex(SubString(@str1,@i,@n), @str2); 
    if @result > 0  return @result;
    set @i=@i+1;
  end;

  return 0;
end;

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35757015
but here is another bug ;)

select dbo.LikeNChar('test', 'testing', 5)
1
select dbo.LikeNChar('testing', 'test', 5)
0

with the fixed version below:

select dbo.LikeNChar('This is a test string', 'I am testing this!', 4)
10
select dbo.LikeNChar('test', 'test', 4)
1
select dbo.LikeNChar('that', 'test another', 4)
0

select dbo.LikeNChar('test', 'testing', 5)
1
select dbo.LikeNChar('testing', 'test', 5)
1

I hope there is no more bugs :)
alter function LikeNChar(@str1 varchar(max), @str2 varchar(max), @n int =  3) returns int
as
begin
  declare @result int;
  declare @i int = 1;
  declare @t varchar(max) = @str1;

  --swap strings, first one should be smaller in length
  if Len(@str1)>len(@str2) 
  begin
    set @str1 = @str2;
    set @str2 = @t;
  end;

  if @n>len(@str1) set @n = len(@str1);
  
  while @i<=len(@str1)-@n+1
  begin
    set @result = CharIndex(SubString(@str1,@i,@n), @str2); 
    if @result > 0  return @result;
    set @i=@i+1;
  end;

  return 0;
end;

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question