Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.sqlsql

Comparing Strings (Advanced Soundex)

Hello,

I need to compare movie names from two systems. In both systems these names are entered manually by operators. I would like to compare them and give a rating on how close these names are equal.

Stripping special characters, and spaces is just not enough. It can happen that they key in sligthly different names. I've tried to use soundex but as we have over 15000 movie titles over the years i'm getting to many equal soundexes to use this as a comparison key.

Any ideas if there are techniques to do this ...

Kind Regards

See if these will help you out any:

http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp

http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp

Comparing range within strings.

Hello, my first post :)

I'm currently working on a project that involves IP checking, here's my scenario:

A user enters an IP address of 10.174.55.65 let's say, I want to somehow be able to check and see if this IP address is in between column A (It's called ip) which consists the IP data of 10.174.0.0 and column B (It's called EndingIp) which has the IP data 10.174.255.255, so generally speaking I'm checking for the 3rd and 4th spots within the IP address and I'm trying to see if it's between the IP's in column A and B.

The thing is these are all strings, so how do I approch a stiuation like that within SQL?

Btw, I attached a picture of the table to be more clear.

Thanks in advance for the advice...... I'm trying to see if it's between the IP's in column A and B.If I understand it well, you could just check withWHERE val BETWEEN a AND bThis will only work if e.g. 10.1.17.32 is written as 010.001.017.032, i.e., if all four entries have 3 digits. In that case "alphabetic" order is the ordering you want.
It's probably more difficult to convert val, A and B to this format, so you will have to extract the four parts with some scalar function, recompose (either as string, in the 4x3 digit form, or as an integer: field1 x 256^3 + field2 x 256^2 + field3 x 256 + field4) and then have the BETWEEN condition.
Available scalar functions for this purpose may differ from system to system, so I won't go into details here.|||if you would kindly mention which database you're using, birko, i'll move this thread to the approproate forum where you may get more specific answers|||I'm sorry if I posted this in the wrong forum, anyways I'm using SQL 2000 server and I kind of fixed the problem of comparing within the range logically, here's what I have in a stored procedure which seems to work for IP range checking:

CREATE PROCEDURE ipRange

-- This procedure checks for the IP range.
@.ipAdd varchar(50) as

SELECT
case
when (@.ipAdd <= '10.174.90.90' and @.ipAdd >= '10.174.80.80')
then 'IP EXISTS'
END

Of course my next step is to get the values from the columns and do the comparring with them, so I'm off to do that :)

Just a background about the project, we have an ASP.NET web application coded in C# and there's a part where the user has to enter an IP address, we have to see whether the IP exists within the database or not and that's where the range check comes in since some customers have a bunch of IP addresses that range from let's say 10.174.0.0 up to 10.174.255.255, so we want to check within the range of the IP addresses in out database, it's a long project but hopfully it can be done soon.

Thank you guys :)

comparing encrypted strings

How do you compare an encripted value to a string?

I have a table called test table which has a column called password. The fields in that column were encrypted using the pwdencrypt() function. I need to be able to compare those encrypted fields to regular nvarchar strings. Right now I am using the pwdcompare() function to compare the values but I'm not getting the desired results.

This is what I am doing

select *
from testtable
where pwdcompare(pwdencrypt('pass_tempx'),testtable.pass word) = 1Check this one:

create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)

insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')

insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')

select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'

Id
----
1

(1 row(s) affected)

select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----|||Thanks for the feedback but it still isn't working for me. I've got my table set up just like yours but my username and userpassword fields are set up as nvarchar(15). Does this make a difference? Is there any other way to compare the 2 strings?

Originally posted by snail
Check this one:

create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)

insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')

insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')

select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'

Id
----
1

(1 row(s) affected)

select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----|||Originally posted by grualo1
Thanks for the feedback but it still isn't working for me. I've got my table set up just like yours but my username and userpassword fields are set up as nvarchar(15). Does this make a difference? Is there any other way to compare the 2 strings?

MS is using nvarchar(128) for keeping a passwors and I guess functions pwdcompare and pwdencrypt are working only for this length of string.|||Changing the length to 128 did the trick thank you so much!

Gary

Originally posted by snail
MS is using nvarchar(128) for keeping a passwors and I guess functions pwdcompare and pwdencrypt are working only for this length of string.

Compare strings in text field

I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.

Heres what I am doing:

I created a table called keywords that contains a reference for each product.

keyword -> varchar(100)
items -> Text

keyword data example:

[keyword] [items]
car 1, 3, 5, 7
blue 3, 5
compact 1,7

I am not using clustered index.

To search basically I run the "AND" or "OR" to select the keywords I want to target.
I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:

looking for car compact using "AND" clause
result = 1

looking for car compact using "OR" clause
result = 1,3,5,7

There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.

I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.

In addition, in this scenario, how should a clustered index help?

Thanks

RodrigoNot sure i get it but from what i understand why do you have a , delimited string of the items

why not a seperate row for each item so that, you will get all the items in a query that you can then use easier...i take it the item is the foreign key for some record in another table|||If I am right, that physically generating the rows would fall in the scenario in the 12 milion scenario leading to unecessary records and lots of sql file pages to hold the data references. I 'd ratter have a little IO because I could cache the results. Do you have any suggestion?

Thanks
Rod

Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
>> hi,
>> We have SQL SERVER 2000 database with a table of customers. We would
>> like to compare the names of customers for double names. We want to
>> find same names or names that are look like.
>> for example:
>> ID NAME ADDRESS
>> 1 John Smith
>> 100 John Smitth
>> We consider that it is a very useful we have a ranking for these names.
>> Have you any idea, how can do this ?.
>|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:
>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for a help.

Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
>|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:

>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for a help.

Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.
Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegr oups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>
|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegr oups.com...
>
|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:

>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you for a help.

Compare Strings

Thanks Julie

>--Original Message--
>Have you checked out the PATINDEX statement ?
>J
>fields,
>have
=[vbcol=seagreen]
>@.ContentID
always[vbcol=seagreen]
>.
>Here is an procedure/operator that returns string differences in each
direction between 2 strings. It can easily be used with sql server.
[url]http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html[/u
rl]
Of course it would be nice if it entices you to explore Dataphor -

Compare Strings

Hello,
I need to compare two strings that are in different fields,
my first step was capture one substring and capture the
result(in this case all the characters inside the string
are equal), but i cant use this because in some records
the substring is different because one of the strings have
more 4 characters.
I send you the two strings and hope that you can help me,
sorry but the two strings are very large
1st string:
(declare @.P1 bigint set @.P1=NULL exec Content_Save
@.ContentID = @.P1 output, @.ScheduleID = 663, @.ContractID = 367, @.IniDate = 'May 31 2004 5:06:04:000PM', @.EndDate
= 'May 31 2004 5:06:07:570PM', @.File = N'WAP UH
Politica_367_663_200405311706_d920a611-1552-
)
2nd string:
(declare @.P1 bigint set @.P1=0 exec Content_Save @.ContentID
= @.P1 output, @.ScheduleID = 663, @.ContractID = 367,
@.IniDate = 'May 31 2004 5:06:04:000PM', @.EndDate = 'May
31 2004 5:06:07:570PM', @.File = N'WAP UH
Politica_367_663_200405311706_d920a611-1552-40f
)
The two strings are always equal near the end of the
string
"....N'.. and the next 45 characters" but the not always
are equal at the begining.
Thanks a lot,
Best RegardsHave you checked out the PATINDEX statement ?
J
>--Original Message--
>Hello,
>I need to compare two strings that are in different
fields,
>my first step was capture one substring and capture the
>result(in this case all the characters inside the string
>are equal), but i cant use this because in some records
>the substring is different because one of the strings
have
>more 4 characters.
>I send you the two strings and hope that you can help me,
>sorry but the two strings are very large
>1st string:
>(declare @.P1 bigint set @.P1=NULL exec Content_Save
>@.ContentID = @.P1 output, @.ScheduleID = 663, @.ContractID =>367, @.IniDate = 'May 31 2004 5:06:04:000PM', @.EndDate
>= 'May 31 2004 5:06:07:570PM', @.File = N'WAP UH
>Politica_367_663_200405311706_d920a611-1552-
>)
>2nd string:
>(declare @.P1 bigint set @.P1=0 exec Content_Save
@.ContentID
>= @.P1 output, @.ScheduleID = 663, @.ContractID = 367,
>@.IniDate = 'May 31 2004 5:06:04:000PM', @.EndDate = 'May
>31 2004 5:06:07:570PM', @.File = N'WAP UH
>Politica_367_663_200405311706_d920a611-1552-40f
>)
>The two strings are always equal near the end of the
>string
>"....N'.. and the next 45 characters" but the not always
>are equal at the begining.
>Thanks a lot,
>Best Regards
>.
>|||Thanks Julie
>--Original Message--
>Have you checked out the PATINDEX statement ?
>J
>>--Original Message--
>>Hello,
>>I need to compare two strings that are in different
>fields,
>>my first step was capture one substring and capture the
>>result(in this case all the characters inside the string
>>are equal), but i cant use this because in some records
>>the substring is different because one of the strings
>have
>>more 4 characters.
>>I send you the two strings and hope that you can help me,
>>sorry but the two strings are very large
>>1st string:
>>(declare @.P1 bigint set @.P1=NULL exec Content_Save
>>@.ContentID = @.P1 output, @.ScheduleID = 663, @.ContractID
=>>367, @.IniDate = 'May 31 2004 5:06:04:000PM', @.EndDate
>>= 'May 31 2004 5:06:07:570PM', @.File = N'WAP UH
>>Politica_367_663_200405311706_d920a611-1552-
>>)
>>2nd string:
>>(declare @.P1 bigint set @.P1=0 exec Content_Save
>@.ContentID
>>= @.P1 output, @.ScheduleID = 663, @.ContractID = 367,
>>@.IniDate = 'May 31 2004 5:06:04:000PM', @.EndDate = 'May
>>31 2004 5:06:07:570PM', @.File = N'WAP UH
>>Politica_367_663_200405311706_d920a611-1552-40f
>>)
>>The two strings are always equal near the end of the
>>string
>>"....N'.. and the next 45 characters" but the not
always
>>are equal at the begining.
>>Thanks a lot,
>>Best Regards
>>.
>.
>

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:
>

compare 2 strings

Hi,
I need to compare 2 strings from 2 different tables but there is no common
column for a join.
CREATE TABLE T1 (FirstName varchar(20), LastName varchar(20))
CREATE TABLE T2 (FirstName varchar(20), LastName varchar(20))
I created 2 temp tables (#T1 & #T2) populated with binary data for the
comparasion but not realy sure how to proceed from there...
CAST(lower(FirstName + LastName) AS VARBINARY) AS FullNameYan
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where
charindex(cast('H' as varbinary(20)),cast(description as varbinary(20)))> 0
"Yan" <yanive@.rediffmail.com> wrote in message
news:OU4JgyrQGHA.4920@.tk2msftngp13.phx.gbl...
> Hi,
> I need to compare 2 strings from 2 different tables but there is no common
> column for a join.
> CREATE TABLE T1 (FirstName varchar(20), LastName varchar(20))
> CREATE TABLE T2 (FirstName varchar(20), LastName varchar(20))
> I created 2 temp tables (#T1 & #T2) populated with binary data for the
> comparasion but not realy sure how to proceed from there...
> CAST(lower(FirstName + LastName) AS VARBINARY) AS FullName
>|||Sorry, I do not understand.
I need to find if any record from #T1 exists in #T2 in order to know if any
name wich exists in #T1 also exists in #T2.
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6G0u3rQGHA.4312@.TK2MSFTNGP12.phx.gbl...
> Yan
> create table ABCD
> (
> courceid smallint not null,
> description varchar(20) null
> )
> insert into ABCD(courceid,description)values (1,'DFh2AcZ')
> insert into ABCD(courceid,description)values (2,'dHZ3')
> )
> SELECT description FROM ABCD where
> charindex(cast('H' as varbinary(20)),cast(description as varbinary(20)))>
> 0
>
>
> "Yan" <yanive@.rediffmail.com> wrote in message
> news:OU4JgyrQGHA.4920@.tk2msftngp13.phx.gbl...
>|||Yan
CREATE TABLE #T1 ( col1 VARCHAR(10)NOT NULL)
CREATE TABLE #T2 ( col1 VARCHAR(10)NOT NULL)
INSERT INTO #T1 VALUES ('Clinton')
INSERT INTO #T1 VALUES ('Bush')
INSERT INTO #T1 VALUES ('Lenin')
INSERT INTO #T1 VALUES ('Putin')
INSERT INTO #T2 VALUES ('Stalin')
INSERT INTO #T2 VALUES ('Ford')
INSERT INTO #T2 VALUES ('Lenin')
INSERT INTO #T2 VALUES ('Putin')
--SQL Server 2000
SELECT * FROM #T1
WHERE col1 NOT IN (SELECT col1 FROM #T2)
--SQL Server 2005
SELECT * FROM #T1 EXCEPT SELECT * FROM #T2;
"Yan" <yanive@.rediffmail.com> wrote in message
news:u5p1$DsQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Sorry, I do not understand.
> I need to find if any record from #T1 exists in #T2 in order to know if
> any name wich exists in #T1 also exists in #T2.
> --
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6G0u3rQGHA.4312@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2361Y8LsQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Yan
> CREATE TABLE #T1 ( col1 VARCHAR(10)NOT NULL)
> CREATE TABLE #T2 ( col1 VARCHAR(10)NOT NULL)
> INSERT INTO #T1 VALUES ('Clinton')
> INSERT INTO #T1 VALUES ('Bush')
> INSERT INTO #T1 VALUES ('Lenin')
> INSERT INTO #T1 VALUES ('Putin')
> INSERT INTO #T2 VALUES ('Stalin')
> INSERT INTO #T2 VALUES ('Ford')
> INSERT INTO #T2 VALUES ('Lenin')
> INSERT INTO #T2 VALUES ('Putin')
> --SQL Server 2000
> SELECT * FROM #T1
> WHERE col1 NOT IN (SELECT col1 FROM #T2)
> --SQL Server 2005
> SELECT * FROM #T1 EXCEPT SELECT * FROM #T2;
>
>
>
>
>
> "Yan" <yanive@.rediffmail.com> wrote in message
> news:u5p1$DsQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>|||On Wed, 8 Mar 2006 17:06:03 +0200, "Yan" <yanive@.rediffmail.com>
wrote:

>I need to find if any record from #T1 exists in #T2 in order to know if any
>name wich exists in #T1 also exists in #T2.
SELECT *
FROM T1
WHERE EXISTS
(select * from T2
where T1.FirstName = T2.Firstname
and T1.LastName = T2.LastName)
Using IN is fine when you only have one column to test. When you have
two columns to test EXISTS is much preferred. You really do NOT want
to concatenate strings together for testing unless there is very
special reason to, such as strange data.
Roy Harvey
Beacon Falls, CT

Compare 2 char strings?

Hi,

How do i compare 2 dates which are char datatypes?

Example:

declare @.d1 char(24)

declare @.d2 char(24)

SET @.d1 ='2007-04-24 00 :00:00:000'

SET @.d2 = '2007-04-24 00 :00:00:000'

IF(@.d1=@.d2)

print '1'

Else

print '2'

output:

out put is 2 instead of 1.

It works fine if i use >= or <= but not =.

--Kodela.

You should be using "==". "=" is an assignment operator, not an equality operator.|||

Phil Brammer wrote:

You should be using "==". "=" is an assignment operator, not an equality operator.

In T-SQL?|||

Adamus Turner wrote:

Phil Brammer wrote:

You should be using "==". "=" is an assignment operator, not an equality operator.

In T-SQL?

True that. Heck, I just jumped in when he said "==" worked. His code worked fine for me in T-SQL as written.|||

When I paste this code into my query analyzer, I get '1'. What you have there should work. Are you always comparing dates?

Mike Binkley.

|||

Hi Phil,

It didn't work for me.I got this error when i used '=='

Incorrect syntax near '='.

--Kodela.|||

Because you're dealing with timestamps that use seconds, no 2 dates will ever be equal.

01/01/2001 01:01:000 will never be equal to 01/01/2001 01:02:000

You must format the date so there is no timestamp hh:mms to compare.

SELECT CONVERT(CHAR(10),GETDATE(),110), CONVERT(CHAR(10),GETDATE(),110)

--Notice the difference:

SELECT CONVERT(CHAR(10),GETDATE(),110), GETDATE()

These above dates are equal. So you must do the following:

declare @.d1 char(10)

declare @.d2 char(10)

SET @.d1 ='2007-04-24 00 :00:01:000'

SET @.d2 = '2007-04-24 00 :03:21:000' --This is still equal to the above date

IF(@.d1=@.d2)

print '1'

Else

print '2'

Setting char(10) instead of char(24) will accomplish the same task

Adamus

|||

kodela wrote:

Hi Phil,

It didn't work for me.I got this error when i used '=='

Incorrect syntax near '='.

--Kodela.

Yeah, I'm blind. I admit it among my other flaws in this post. Should've kept my fingers away from the keyboard... |||

It worked for you because the seconds are the same. The poster didn't realize he posted correct syntax on accident. If you change the seconds the code will not work.

declare @.d1 char(24)

declare @.d2 char(24)

SET @.d1 ='2007-04-24 00 :00:00:001'

SET @.d2 = '2007-04-24 00 :00:00:000'

IF(@.d1=@.d2)

print '1'

Else

print '2'

Notice the "001" on the seconds. You have to use char(10)

Adamus

|||

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

|||

kodela wrote:

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

Then you cannot use an = comparison unless you first compare the date and then the time. You are forced to use <= or >= or BETWEEN

But be careful with BETWEEN when using timestamps because it will exclude remainders of days.

Adamus

|||

kodela wrote:

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

Then the above will work just fine. Just know that if it's one second off, the comparison will not be equal.|||

kodela wrote:

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

Convert to Char(10) to know the days are the same --> then compare Char(24)

Adamus

|||

Do you want to compare DATES or do you want to compare DATE STRINGS?

Code Snippet

declare @.dt1 datetime

declare @.dt2 datetime

declare @.str1 char(24)

declare @.str2 char(24)

--Set the STRING values (notice "A" in april comes before 'N' in 'November)

SET @.str1 ='November 1, 2007'

SET @.str2 = 'April 1, 2008'

--IMPLICIT conversion of STRING values to DateTime values

set @.dt1 = @.str1

set @.dt2 = @.str2

if @.str1 > @.str2

select 'String 1 Is Bigger'

if @.str2 > @.str1

select 'String 2 is Bigger.'

if @.dt1 > @.dt2

select 'Date 1 is Bigger'

if @.dt2 > @.dt1

select 'Date 2 is Bigger'

|||

I'm not sure how this would work at all. What if you're comparing dates in the same month? ...and the month are not in alpabetical order.

Adamus