Select only fields that match the query

[Jozza]

I have three text fields in a table that i search for specific text. If
field 1 contains the match, how can i know, on the fly, that it was field 1
that contained the match and not field 2? Should i browse manualy through
the result set or is there a trick to do it?

The query so far is: select * from table where (Field1 like '%text%') or
(Field2 like '%text%') or (Field3 like '%text%')

I use a single query input to find results, thats why i search all available
fields in a table. I dont want to use any other server specific functions
but sql to perform the search. The reason i need to know which field it was
is, so i can display a part of the text that contained the match.

THX, J

[Adi]

Post by Jozza
I have three text fields in a table that i search for specific text. If
field 1 contains the match, how can i know, on the fly, that it was field 1
that contained the match and not field 2? Should i browse manualy through
the result set or is there a trick to do it?
The query so far is: select * from table where (Field1 like '%text%') or
(Field2 like '%text%') or (Field3 like '%text%')
I use a single query input to find results, thats why i search all available
fields in a table. I dont want to use any other server specific functions
but sql to perform the search. The reason i need to know which field it was
is, so i can display a part of the text that contained the match.
THX, J

I think that if you don't want to use any functions such as patindex
or anything that won't work on a different server, then you'll have to
use the client to check the resultset that you got.

Adi

Adi

[j***@googlemail.com]

Hi J,

Do you need to return all three columns if only 1 of them contains the
text? How about using CASE to only return the column that has
matching text?

Something like:
SELECT
CASE
WHEN Field1 LIKE '%text%'
THEN Field1
WHEN Field2 LIKE '%text%'
THEN Field2
ELSE Field3
END AS TextMatch
FROM table
WHERE Field1 LIKE '%text%'
OR Field2 LIKE '%text%'
OR Field3 LIKE '%text%'

Seems kinda messy though - and what do you want to happen if more than
1 of the text fields contains the search string? Could you supply
additional context?

Good luck!
J

[Paddy]

Select
* from

(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3



)t
where t.Newfield like '%%'

[Jozza]

Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...

Thx Paddy and all the others for the effort

J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'

[Adi]

Post by Jozza
Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...
Thx Paddy and all the others for the effort
J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'- Hide quoted text -

- Show quoted text -

Notice that this will result in 3 table scans (or index scans if you
have indexes that are based on those columns). Depending on the
table's size and the amount or records that you think that will be
returned to the user, it might perform better if the client will check
the columns.

Adi

[Jozza]

Noted. Good advice

Post by Adi

Post by Jozza
Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...
Thx Paddy and all the others for the effort
J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'- Hide quoted text -

- Show quoted text -

Notice that this will result in 3 table scans (or index scans if you
have indexes that are based on those columns). Depending on the
table's size and the amount or records that you think that will be
returned to the user, it might perform better if the client will check
the columns.
Adi

[Tom Cooper]

You can get the same result while only scanning the table once with

Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From yourtable
Cross Join (Select 1 As Number
Union All Select 2
Union All Select 3) As n

Tom

Post by Jozza
Noted. Good advice

Post by Adi

Post by Jozza
Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...
Thx Paddy and all the others for the effort
J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'- Hide quoted text -

- Show quoted text -

Notice that this will result in 3 table scans (or index scans if you
have indexes that are based on those columns). Depending on the
table's size and the amount or records that you think that will be
returned to the user, it might perform better if the client will check
the columns.
Adi

[Adi]

On Dec 13, 8:31 pm, "Tom Cooper"

Post by Tom Cooper
You can get the same result while only scanning the table once with
Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From yourtable
Cross Join (Select 1 As Number
Union All Select 2
Union All Select 3) As n
Tom

Post by Jozza
Noted. Good advice

Post by Adi

Post by Jozza
Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...
Thx Paddy and all the others for the effort
J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'- Hide quoted text -

- Show quoted text -

Notice that this will result in 3 table scans (or index scans if you
have indexes that are based on those columns). Depending on the
table's size and the amount or records that you think that will be
returned to the user, it might perform better if the client will check
the columns.
Adi- Hide quoted text -

- Show quoted text -

I'm sorry Tom, but I don't see how this should work. Can you
elaborate about your solution?

Adi

[Tom Cooper]

Post by Adi
On Dec 13, 8:31 pm, "Tom Cooper"

Post by Tom Cooper
You can get the same result while only scanning the table once with
Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From yourtable
Cross Join (Select 1 As Number
Union All Select 2
Union All Select 3) As n
Tom

Post by Jozza
Noted. Good advice

Post by Adi

Post by Jozza
Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...
Thx Paddy and all the others for the effort
J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'- Hide quoted text -

- Show quoted text -

Notice that this will result in 3 table scans (or index scans if you
have indexes that are based on those columns). Depending on the
table's size and the amount or records that you think that will be
returned to the user, it might perform better if the client will check
the columns.
Adi- Hide quoted text -

- Show quoted text -

I'm sorry Tom, but I don't see how this should work. Can you
elaborate about your solution?
Adi

OK, you got me. My answer was certainly incomplete.

The first thing I should have said is that my answer was not an answer to
the OP's original question, which I would probably do differently, but
rather a way to achieve the same results as Paddy's suggestion, that is,

Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'

but avoid the multiple scans of the table that come from the unions.

The way my query works is it takes the table and does a cross join with a
derived table. That derived table has 3 rows and a cross join matches every
row in the first table with every row in the second. So after the cross
join you have three copies of the data from the original table, but the
original table was only scanned once. Then the case statement is used to
select Field1 from the first copy, Field2 from the second copy, etc.

Also I needed to add a DISTINCT to exactly duplicate what Paddy was doing
since he was using UNION rather than UNION ALL.

The following is an example of how all this works using a temporary table,
changing Paddy's query to have the FROM clauses it needs, and putting in the
changes necessary to mine to make it complete.

First create a temporary table and load it with some data.

Create Table #yourtable (
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10))
go
Insert #yourtable (Field1, Field2, Field3)
Values ('Row1Field1', 'Row1Field2', 'Row1Field3')
Insert #yourtable (Field1, Field2, Field3)
Values ('Row2Field1', 'Row2Field2', 'Row2Field3')
Insert #yourtable (Field1, Field2, Field3)
Values ('Row3Field1', 'Row3Field2', 'Row3Field3')
Insert #yourtable (Field1, Field2, Field3)
Values ('Row4Field1', 'Row4Field2', 'Row4Field3')

Then Paddy's query would look like:
Select
* from
(
Select Field1 as NEWFIELD From #yourtable
union
SElect Field2 From #yourtable
union
select field3 From #yourtable
)t
where t.Newfield like '%%'

And my complete query would look like
Select Distinct NewField
From (Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From #yourtable
Cross Join (Select 1 As Number
Union All Select 2
Union All Select 3) As n) As t
Where NewField Like '%%'

If you run them both, you will see they get the same results. If you look
at the execution plans, you will see that my query scans the table
#yourtable one time and that the estimated cost of my query is about 1/3 of
the estimated cost of Paddy's which scans the table 3 times.

It should also be noted that if you have a Numbers table - see
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
then you can use it rather than the derived table to get the numbers from 1
to 3, e.g.,

Select Distinct NewField
From (Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From #yourtable
Inner Join Numbers On Number <= 3) As t
Where NewField Like '%%'

Tom

[Paddy]

Crikey. I would never of thought of doing all that, thanks for the
detailed explanation Tom - This will come in useful.

Paddy

[Adi]

On Dec 14, 5:09 pm, "Tom Cooper"

Post by Tom Cooper

Post by Adi
On Dec 13, 8:31 pm, "Tom Cooper"

Post by Tom Cooper
You can get the same result while only scanning the table once with
Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From yourtable
Cross Join (Select 1 As Number
Union All Select 2
Union All Select 3) As n
Tom

Post by Jozza
Noted. Good advice

Post by Adi

Post by Jozza
Yes, that does the job, to make it actualy work i had to add "from table" to
all inner select statements. You made me think...
Thx Paddy and all the others for the effort
J.

Post by Paddy
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'- Hide quoted text -

- Show quoted text -

Notice that this will result in 3 table scans (or index scans if you
have indexes that are based on those columns). Depending on the
table's size and the amount or records that you think that will be
returned to the user, it might perform better if the client will check
the columns.
Adi- Hide quoted text -

- Show quoted text -

I'm sorry Tom, but I don't see how this should work. Can you
elaborate about your solution?
Adi

OK, you got me. My answer was certainly incomplete.
The first thing I should have said is that my answer was not an answer to
the OP's original question, which I would probably do differently, but
rather a way to achieve the same results as Paddy's suggestion, that is,
Select
* from
(
Select Field1 as NEWFIELD
union
SElect Field2
union
select field 3
)t
where t.Newfield like '%%'
but avoid the multiple scans of the table that come from the unions.
The way my query works is it takes the table and does a cross join with a
derived table. That derived table has 3 rows and a cross join matches every
row in the first table with every row in the second. So after the cross
join you have three copies of the data from the original table, but the
original table was only scanned once. Then the case statement is used to
select Field1 from the first copy, Field2 from the second copy, etc.
Also I needed to add a DISTINCT to exactly duplicate what Paddy was doing
since he was using UNION rather than UNION ALL.
The following is an example of how all this works using a temporary table,
changing Paddy's query to have the FROM clauses it needs, and putting in the
changes necessary to mine to make it complete.
First create a temporary table and load it with some data.
Create Table #yourtable (
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10))
go
Insert #yourtable (Field1, Field2, Field3)
Values ('Row1Field1', 'Row1Field2', 'Row1Field3')
Insert #yourtable (Field1, Field2, Field3)
Values ('Row2Field1', 'Row2Field2', 'Row2Field3')
Insert #yourtable (Field1, Field2, Field3)
Values ('Row3Field1', 'Row3Field2', 'Row3Field3')
Insert #yourtable (Field1, Field2, Field3)
Values ('Row4Field1', 'Row4Field2', 'Row4Field3')
Select
* from
(
Select Field1 as NEWFIELD From #yourtable
union
SElect Field2 From #yourtable
union
select field3 From #yourtable
)t
where t.Newfield like '%%'
And my complete query would look like
Select Distinct NewField
From (Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From #yourtable
Cross Join (Select 1 As Number
Union All Select 2
Union All Select 3) As n) As t
Where NewField Like '%%'
If you run them both, you will see they get the same results. If you look
at the execution plans, you will see that my query scans the table
#yourtable one time and that the estimated cost of my query is about 1/3 of
the estimated cost of Paddy's which scans the table 3 times.
It should also be noted that if you have a Numbers table - seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...
then you can use it rather than the derived table to get the numbers from 1
to 3, e.g.,
Select Distinct NewField
From (Select Case When Number = 1 Then Field1
When Number = 2 Then Field2
When Number = 3 Then Field3 End As NewField
From #yourtable
Inner Join Numbers On Number <= 3) As t
Where NewField Like '%%'
Tom- Hide quoted text -
- Show quoted text -

Very impressive:-). Thank you for the explanation. This is one of
the few times that I see a good use of cross join.

Adi

[s***@gmail.com]

this is work tq very much for the sharing