SQL Match at 5 characters - 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 2005 Forums

Transact-SQL (2005)

SQL Match at 5 characters

Author

Topic

jujuju918
Starting Member

3 Posts

Posted - 2008-10-22 : 18:07:48

Hello,

Is there a simple way to select all from a table where a column matches at least 5 specified characters?

Thanks,
Julius

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 18:14:35

Characters in the given order without a gap?
select * from table where column1 like '%webfr%'

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

jujuju918
Starting Member

3 Posts

Posted - 2008-10-22 : 18:19:41

Well let me rephrased that. I want to compare two colunmn and return only those records that have at least 5 matching character in a row.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 18:56:26

But, You said Specified... that's different than matching two columns on any 5 chars match in a row.

If you meant specified. Such as you want to match 'WEBFR' in both Col1 and Col2 then
WHERE COL1 like '%WEBFR%' and COL2 like '%WEBFR%'

If you meant to match any 5 characters in a row between the two columns then you'll need some serious logics to go in. Which I am going to work on it in the next hour.

jujuju918
Starting Member

3 Posts

Posted - 2008-10-22 : 19:03:55

Yes you are correct hanbingl. This is exactly what I need. I will await your logic. Thank very much!!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 19:28:43

[code]
create function dbo.fn_match5(@col1 varchar(8000), @col2 varchar(8000))
returns varchar(8000)
as
begin

declare @fivechars varchar(5)
declare @startlen int, @len int, @match int
set @startlen = 1
select @len = len(@col1)-4

while @startlen <= @len
begin
select @fivechars = substring(@col1, @startlen, 5)
set @startlen = @startlen + 1

if patindex('%'+@fivechars+'%', @col2) > 0
begin
return @fivechars
end

end
return ''

end[/code]

TEST DATA AND USAGE
[code]
declare @sample table (id bigint identity(1,1) not null primary key, col1 varchar(10), col2 varchar(10))

insert into @sample values('ABCDEFGHIJ','CDEFGABHI')
insert into @sample values('ABZDEFGHIJ','ZDEFGABHI')
insert into @sample values('ABZDEFGHIJ','ZDEFdABHI')

select col1, col2, left(dbo.fn_match5(col1, col2),5) from @sample

col1 col2
---------- ---------- -----
ABCDEFGHIJ CDEFGABHI CDEFG
ABZDEFGHIJ ZDEFGABHI ZDEFG
ABZDEFGHIJ ZDEFdABHI

(3 row(s) affected)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources