String match number of different columns

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)

String match number of different columns

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-24 : 09:24:18

Hi,

In short:
I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)

Problem:
I got a set of rows with varchar(50), spread out over multiple tables.
All those tables relate to a central Colour table.
For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id

E.g: input: 'BLACK', 'MERCEDES', '1984'
Would return colour ids "025864", 45987632", "65489" and "63249"
Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.

Current Situation:
I) Create a table containing all possible values
CREATE TABLE dbo.CommonSearch(
id int IDENTITY (1, 1) NOT NULL,
clr_id int,
keyWord varchar(40),
fieldType varchar(25)
And fill it with all the values (671694 rows)
)
II) Stored Procedure to cut a string up into a table:
CREATE FUNCTION dbo.SplitString
(
@param varchar(50),
@splitChar char = ' '
)
RETURNS
@T TABLE (keyWord varchar(50))
AS
BEGIN
WHILE LEN( @param ) > 0 BEGIN
declare @val varchar(50)
IF CHARINDEX( @splitChar, @param ) > 0
SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) ,
@param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) )
ELSE
SELECT @val = @param, @param = SPACE(0)
INSERT INTO @T values (@val)
END
RETURN
END
III)Stored Procedure to query the first table with the second one
CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings]
@keyWords varchar(255) = ''
AS
BEGIN
SET NOCOUNT ON;
select clr_id, keyWord, fieldType
from dbo.commonSearch
where keyWord in (select * from splitString(@keyWords, ' '))
END

So, how can I use a LIKE statement in the IN statement of the last query.
Furthermore, I was wondering if this is the best sollution to go for.
Are there any better methods? Got any tuning tips to squeeze out an extra second?

cruxmagi
Starting Member

38 Posts

Posted - 2008-04-24 : 09:40:42

make a while loop for where clause

1)keep your query as a varchar
attach the condition loop with query

example

declare @condn varchar(200)
set @condn = 'where ('
declare c1 cursor
for select * from splitString(@keyWords, ' ')
declare @keyword varchar(30)
open c1
fetch next from c1 into @keyword
while @@fetch_status !=-1
begin
set @condn = @condn + 'keyWord like ''%' +@keyword+' or '
end
close c1
deallocate c1

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-24 : 10:51:48

Won't that hit my performance big time?
I imagine all that string mashing vs an IN operator would make a BIG difference.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-24 : 13:46:37

Hi Boris

Could you give us some example data and the output you'd expect for that sample data? I know you gave an example, but I'm not sure how to relate that to your structure...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-25 : 08:24:30

An excerpt from the erd (sorry for the 1 after each name, it's a visio thing)

I would want to input for example "Merc" and "A-Series"
As a result I want a set of colours (clr) that relate to everything that contains Merc and A-series in either of the following fields:

co.long_nm
clr_use_yr.yr_num
modl.modl_nm
paint_cd.paint_cd
clr_nm.clr_nm

Speed is (as always) the keyword here.

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-28 : 08:26:07

Does that make it more clear?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources