Solved

# like match on 5 chars either side

Posted on 2011-05-11
Medium Priority
333 Views
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
Question by:elucero
• 11
• 5
• 2
• +2

LVL 61

Expert Comment

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

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
``````
0

Author Comment

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

LVL 61

Expert Comment

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

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

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
``````
0

LVL 61

Expert Comment

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

ID: 35740637
:)
0

Author Comment

ID: 35741080
yes, I got it thanks
0

LVL 61

Expert Comment

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

ID: 35741724
Agree with HainKurt.
0

LVL 21

Expert Comment

ID: 35745819
where CHARINDEX(ProductName,'Ch',1) > 0
0

LVL 32

Expert Comment

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

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

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
``````
0

LVL 61

Expert Comment

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;
``````
0

LVL 32

Expert Comment

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

Author Comment

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

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;
``````
0

LVL 61

Expert Comment

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;
``````
0

LVL 61

Expert Comment

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;
``````
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month15 days, left to enroll