compare 2 strings

i have a Company table . one of the column is EmailAdres. now i want to
compare a string(a emaiadres) agains this column. the problem is that the
string is different every time.
e.g.
search Parameter1.: "test user"<testuser@.yahoo.com>
search Parameter2.: <testuser@.yahoo.com>
search Parameter3.: (testuser@.yahoo.com)
column EmailAdres from DB :testuser@.yahoo.com
is there a way to do this?Try:
WHERE EmailAdres LIKE '%testuser@.yahoo.com%'
Note that this will require a table/index scan.
Hope this helps.
Dan Guzman
SQL Server MVP
"henk" <henk@.discussions.microsoft.com> wrote in message
news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?|||henk
Have a look at LIKE function in the BOL
"henk" <henk@.discussions.microsoft.com> wrote in message
news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?|||"henk" <henk@.discussions.microsoft.com> wrote in message
news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?
You use parameter for example @.email and compare parameter values with
values on column.
Later input values into parameter.|||In addition to what Dan suggested:
The most efficient solution would be a data clean-up. In an efficient
production environment this is done before the data is inserted.
Once you identify all patterns it should be pretty simple to remove unwanted
characters - perhaps even in a computed column if the source data must remai
n
unchanged.
ML
http://milambda.blogspot.com/|||try this.
Select * from tbl1
where @.searchparam like '%' + email_col + '%'
hope this helps.
"henk" wrote:

> i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?|||thanks to you all for ur fats reply.
i just have a question for dan.
what you exactly mean with "Note that this will require a table/index scan."
lets say my table have about 20,000 records. and i have about 5 email column
s.
does it slow down the process? is it better if i clean up the email like
sugessted in front end and than pass the string to DB.
i still have to use like even if i clean up the search parameter.
what are you suggesting?
thanks in advance.
"Dan Guzman" wrote:

> Try:
> WHERE EmailAdres LIKE '%testuser@.yahoo.com%'
> Note that this will require a table/index scan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "henk" <henk@.discussions.microsoft.com> wrote in message
> news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>
>|||> i still have to use like even if i clean up the search parameter.
So you need to provide 'partial' email search functionality rather than an
exact match? The bottom line is that a LIKE expression with a leading
wildcard will require a scan. 20,000 rows isn't really that big nowadays so
if this is a query that is not run often, I'd just take the performance hit
and be done with it.
You can add a covering index if this query is run often. A scan of a
relatively narrow non-clustered index is less expensive than a table scan
when a relatively small number of rows satisfy the criteria. If you always
search all 5 email columns, a single index with all 5 email columns would be
best.
Without the leading wildcard SQL Server can use indexes to efficiently. An
equality search is the most efficient. You could then normalize your data
without a performance hit. For example:
SELECT *
FROM Company
WHERE EXISTS
(
SELECT *
FROM CompanyEmailAddresses
WHERE CompanyEmailAddresses.CompanyID = Company.CompanyID AND
CompanyEmailAddresses.EmailAdres = @.EmailAdres
)
Hope this helps.
Dan Guzman
SQL Server MVP
"henk" <henk@.discussions.microsoft.com> wrote in message
news:BB8C9D4B-10F4-481F-93AD-B740D77B08C6@.microsoft.com...
> thanks to you all for ur fats reply.
> i just have a question for dan.
> what you exactly mean with "Note that this will require a table/index
> scan."
> lets say my table have about 20,000 records. and i have about 5 email
> columns.
> does it slow down the process? is it better if i clean up the email like
> sugessted in front end and than pass the string to DB.
> i still have to use like even if i clean up the search parameter.
> what are you suggesting?
> thanks in advance.
> "Dan Guzman" wrote:
>