question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Concatenated fields in where clause

I have a script that needs to concatenate fields in the where clause, on top of this it's got to be a 'like' clause and not 'equal to'. For example: select t1.field1, t2.field2, t1.field1 + t2.field2 from table1 as t1 join table2 as t2 on t1.field1 = t2.field1 where t1.field1 + t2.field2 like '%fre%jo%' What is the most efficient way on the server to pull this data back? Just to make life more interesting this needs to run on a SQL2000 server. ** Edit ** Edited code to show that the fields come from more than one table Any feedback gratefully received. Thanks
sql-server-2000concatenation
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ouch!... good luck :)
2 Likes 2 ·
Håkan Winther avatar image
Håkan Winther answered
Maybe you should look into indexed views, but on The other hand your '%search%' can't use an index anyway.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, I think the fact that you cannot invoke indexes may make the point almost moot. It may be worth tring the indexed view to see how substantial the savings from not needing to do the concatenation on the fly might be though.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Firstly I'd select this all into a temp table and then compare the combined column to the required string. As an alternative you might find that a Full Text index is a good way to go. How many rows, what datatypes? How often will the code run?
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is an interesting dynamic... hubby answers wife's question on a technical board? :-)
1 Like 1 ·
Potentially about 70000 rows and very regularly. Many thanks
0 Likes 0 ·
We used to work in the same office and I could just turn round and shout!!
0 Likes 0 ·
@aaronbertrand - The shouts are less frequent nowadays and I dont feel obliged to answer when its at a distance ;)
0 Likes 0 ·
AaronBertrand avatar image
AaronBertrand answered
If you're going to be querying this data a lot more than writing it, I would consider a computed column (maybe persisted?) that holds column1 + column2. However because you will be searching with a wildcard, all the indexes in the world aren't going to help you.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you do this where the fields come from more than one table?
0 Likes 0 ·
Oops, sorry, it wasn't clear they were from multiple tables because I only looked at the first line and your column names didn't have prefixes. :-)
0 Likes 0 ·
I know I'm sorry, I added that in as I realised it wasn't clear enough.
0 Likes 0 ·
liking the computed column idea, given the declared row count and frequency
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.