Query across all colums

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassanot really|||Ok, thanks was worth a question.|||If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

select subA.APKey, sbuB.BPKey
from
(select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
full outer join
(select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
on subA.ChecksumA = subB.ChecksumB
where ...|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:|||While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5dro.asp).

-PatP|||pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

:)|||From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

-PatP|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:

Could be. Without a doubt he needs to be more specific.|||I doubt it though....

I think they're looking for duplicates...

Or they're just trying to establish a join between to related tables, just in the keys?|||Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).|||Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!|||Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
I hope that clears things up. ;)

How would this be possible with full text indexing? Do I magically get some kind of search field?

Shabassa

Query

hey,
i have two tables that I want to match, the records that match I want to delete them from the original file and copy them in an other Tabl
Example
Table A Table
1A 3
3B 4
5J 1
All the records that match in that example (1A and 3B), I want to copy them in a new table and delete them only form TableB
I made a join between the two tables, and I have good results. But how to copy and delete them I don't know
Or maybe in Table B I can have a flag that I put on when it is a mached record? But that I nether don't know how to do that in my join query
Thks for help
JacTo copy them to a new table use SELECT.. INTO:
SELECT col1
INTO NewTable
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
To delete them:
DELETE TableB
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jac" <anonymous@.discussions.microsoft.com> wrote in message
news:4B32C95B-5EB4-4F21-8B58-EF0121E17D02@.microsoft.com...
> hey,
> i have two tables that I want to match, the records that match I want to
delete them from the original file and copy them in an other Table
> Example:
> Table A Table B
> 1A 3B
> 3B 4V
> 5J 1A
>
> All the records that match in that example (1A and 3B), I want to copy
them in a new table and delete them only form TableB.
> I made a join between the two tables, and I have good results. But how to
copy and delete them I don't know?
> Or maybe in Table B I can have a flag that I put on when it is a mached
record? But that I nether don't know how to do that in my join query.
> Thks for help.
> Jacsql

Query

hey,
i have two tables that I want to match, the records that match I want to del
ete them from the original file and copy them in an other Table
Example:
Table A Table B
1A 3B
3B 4V
5J 1A
All the records that match in that example (1A and 3B), I want to copy them
in a new table and delete them only form TableB.
I made a join between the two tables, and I have good results. But how to co
py and delete them I don't know?
Or maybe in Table B I can have a flag that I put on when it is a mached reco
rd? But that I nether don't know how to do that in my join query.
Thks for help.
JacTo copy them to a new table use SELECT.. INTO:
SELECT col1
INTO NewTable
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
To delete them:
DELETE TableB
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Jac" <anonymous@.discussions.microsoft.com> wrote in message
news:4B32C95B-5EB4-4F21-8B58-EF0121E17D02@.microsoft.com...
> hey,
> i have two tables that I want to match, the records that match I want to
delete them from the original file and copy them in an other Table
> Example:
> Table A Table B
> 1A 3B
> 3B 4V
> 5J 1A
>
> All the records that match in that example (1A and 3B), I want to copy
them in a new table and delete them only form TableB.
> I made a join between the two tables, and I have good results. But how to
copy and delete them I don't know?
> Or maybe in Table B I can have a flag that I put on when it is a mached
record? But that I nether don't know how to do that in my join query.
> Thks for help.
> Jac