MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?
Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegr oups.com...
>
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>
|||Thanks.

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar lenth-9
COMPANY - nvarchar lenth-30
NOTES - nvarchar length-250

Select * from database
where NOTES like '%something%'

Is there a way to get results from this query in less then 1-2 second
and how?Hello
I think that using like it would be impossible.
You will get better results when you use full text search (read about it in
books online), however I have not experience with looking for a phrase, but
with single words it works fast.
Alwik

> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ID - nvarchar lenth-9
> COMPANY - nvarchar lenth-30
> NOTES - nvarchar length-250
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?|||On a Athon3200+ (32 bits) home computer
it takes 1692ms to search something like '%RRIDA%' on 393951 rows
table. The maximum length of a row is 3576 bytes. So you only need a
faster CPU and faster memory controler and enough memory to hold the
data pages in memory to achive subsecond time. But IMHO I think this
kind of search is a nonsense for this number of rows.|||So what kind of search are you recommend?|||Am 14 Oct 2005 12:33:10 -0700 schrieb nydefender:

> So what kind of search are you recommend?

What hardware do you use? And how long does it last to get the result? Have
you tried it with an index on NOTES? And i think, a second search should be
much faster then the first one. If you always search on NOTES maybe you can
hold a second table with only PK and field NOTES, which is redundant
(managed by triggers) but can be pinned into memory (DBCC PINTABLE() -
maybe a silly idea, only brainstorming).
Sometimes i have the same problem to find some records out of a big table
where it lasts up to 30 seconds. At first the user knows from
training/docu, that this could need a "long" time to proceed, second i show
a window with a wait-message and something blinking in it, so the user has
not the feeling that the program hangs.

bye,
Helmut|||helmut woess (hw@.iis.at) writes:
> (managed by triggers) but can be pinned into memory (DBCC PINTABLE() -
> maybe a silly idea, only brainstorming).

Yes, DBCC PINTABLE was really a silly idea of Microsoft/Sybase. (Don't
really know who came up with it.) So silly, that in fact in SQL 2005, the
command DBCC PINTABLE is a no-op that performs nothing.

If a table is referenced often enough, it will be in cache anyway, so
PINTABLE has no effect. But if you pin a large table of which only portions
are referenced with some frequency, this means that you are wasting memory
that could have been used for other table, and thus degrade performance.

The only point I can see with PINTABLE is that you have table that you
query so rarely, that it will fall out of the cache. But when you need to
query it, you need the answers snap.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You want subsecond performance for your query. And the query can return
thousands of rows. How many time the clerk will spend searching for the
correct row?. subsecond querys are needed for routine operations and
they return only the necessary information to do the task, if not, the
worker is wasting his time. When you look for %something%, do you
really know what you are looking for?

In an hospitalizaton patient table, if I look for %seropositive% in the
observations field or even for %positive% I'm pretty sure its for a
report or an adhoc decission suport query and this doesn't need
subsecond response time. SQL Server is an OLTP system, designed for a
lot of small transactions, and this kind of queries is an incorrect use
of the system in my opinion.

You sould use something like Microsoft Search Service or a similar
product.|||Maybe this kind of query is "incorect" but is necessary. Now this query
takes for about 15-20 secs. I try to find a better way. I will try with
full text search.
Thanks to all of you.

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?
Full Text index this table and run a full population.
The query would look like this
select * from database where contains(NOTES,'something')
Use the wizard to build the FTS index on your table and make sure you run a
full population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<scgwebmaster@.yahoo.com> wrote in message
news:1129307133.782947.259880@.f14g2000cwb.googlegr oups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegroups.com...
>
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks.

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegroups.com...
>
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks.sql

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or tw

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?You need to use a Full Text Index to do that kind of search quickly. Look
it up in Books Online.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306366.597973.143180@.g43g2000cwa.googlegroups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Pattern matched searching when the wild card is prefixed to the parameter
cannot use an index and so in most cases, a table scan in employed. If this
is something critical, you might want to look into full text indexing
options.
If you know the pattern upfront, one trick you can use like create a
computed column representing the part of the string and indexing the column.
Anith|||To begin with, insure that NOTES is indexed.
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Performing a LIKE search on '%something%' will not efficeintly utilize an
index on NOTES, however, 'something%' would.
http://msdn.microsoft.com/library/d...dcharacters.asp
If you need to perform fast 'wildcard' type searches, then consider
implemeting Index Server and full-text search. It is a service that runs
along side SQL Server. Just remember that the predicates CONTAINS and
FREETEXT are used for free-text searches, so it will involve making
revisions to some of your queries.
http://msdn.microsoft.com/library/d...r />
_3rqg.asp
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306366.597973.143180@.g43g2000cwa.googlegroups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks

MS SQL Server 2000 - Search a table with 250,000+ records in less then a second

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
----
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
----
Is there a way to get results from this query in less then 1-2 second
and how?A few posts below is a remarkably similar question, only the table has
300,000+ rows. I think it applies to your case as well.
<scgwebmaster@.yahoo.com> wrote in message
news:1129305896.814659.100590@.g43g2000cwa.googlegroups.com...
>I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ----
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> ----
> Is there a way to get results from this query in less then 1-2 second
> and how?
>

MS SQL full-text index search

First of all I'm new to MS SQL, I did work with mySQL

Table name db (real db has 12 columns)

Id c1 c2 c3

1 tom john olga

2 tom john olga bleee

I enabled full text index on all columns

Problem when I do search like this:

SELECT * FROM db WHERE CONTAINS(*,'"tom" AND "john"')

It will return only one row (id 2) – I understand that the full text search does look only at one column at a time because it did not return row #1

Anyway I thought that I can add extra column c4 and when user enters new data it will save data from columns c1, c2, c3 to c4 (varchar(750)) and then I will do search only on c4 – this way it will work the way I want.

1) Is there any better way to do this?

2) How do I sort results by "rank" with SQL

Anyone please - Is there any better way to do this?

MS SQL dealing with duplicate columns in rows?

Hello,

Suppose I have the following table...

name employeeId email
--------------
Tom 12345 tom@.localhost.com
Hary 54321
Hary 54321 hary@.localhost.com
I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

Many thanks

YasOn Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
Suppose I have the following table...
>
name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com
>
I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.
>
I wouuld like to say WHERE no blank fields are present to get the
right row to return.
>
Many thanks
>
Yas

Which row for Hary do you want to be returned? The one without an
email address or the one with the email address?|||On 21 Aug, 11:20, stephen <m0604...@.googlemail.comwrote:

Quote:

Originally Posted by

On Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

Suppose I have the following table...

>

Quote:

Originally Posted by

name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com

>

Quote:

Originally Posted by

I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

>

Quote:

Originally Posted by

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

>

Quote:

Originally Posted by

Many thanks

>

Quote:

Originally Posted by

Yas

>
Which row for Hary do you want to be returned? The one without an
email address or the one with the email address?

basically 1 that doesn't have any fields missing...

cheers|||"Yas" <yasar1@.gmail.comschreef in bericht
news:1187701509.682453.306820@.o80g2000hse.googlegr oups.com...

Quote:

Originally Posted by

On 21 Aug, 11:20, stephen <m0604...@.googlemail.comwrote:

Quote:

Originally Posted by

>On Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:
>>
>>
>>

Quote:

Originally Posted by

Hello,

>>

Quote:

Originally Posted by

Suppose I have the following table...

>>

Quote:

Originally Posted by

name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com

>>

Quote:

Originally Posted by

I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

>>

Quote:

Originally Posted by

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

>>

Quote:

Originally Posted by

Many thanks

>>

Quote:

Originally Posted by

Yas

>>
>Which row for Hary do you want to be returned? The one without an
>email address or the one with the email address?

>
basically 1 that doesn't have any fields missing...
>
cheers
>

SELECT * from table where name<>"" and employeeId<>0 and email<>"";

but why would you have this second row "Hary 54321 " in your
table anyway?

would it not be better to create unique index on emplyeeId ?|||"Yas" <yasar1@.gmail.comwrote in message
news:1187701509.682453.306820@.o80g2000hse.googlegr oups.com...

Quote:

Originally Posted by

On 21 Aug, 11:20, stephen <m0604...@.googlemail.comwrote:

Quote:

Originally Posted by

>On Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:
>>
>>
>>

Quote:

Originally Posted by

Hello,

>>

Quote:

Originally Posted by

Suppose I have the following table...

>>

Quote:

Originally Posted by

name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com

>>

Quote:

Originally Posted by

I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

>>

Quote:

Originally Posted by

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

>>

Quote:

Originally Posted by

Many thanks

>>

Quote:

Originally Posted by

Yas

>>
>Which row for Hary do you want to be returned? The one without an
>email address or the one with the email address?

>
basically 1 that doesn't have any fields missing...
>
cheers
>

What is the key of your table? If you don't have a key then you need to fix
the design before you can expect a reasonable solution in SQL.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Hello,
>
Suppose I have the following table...
>
name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com
>
I only want unique employeeIds return. If I use Distinct it will

still

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.
>
I wouuld like to say WHERE no blank fields are present to get the
right row to return.
>
Many thanks
>
Yas
>
Which row for Hary do you want to be returned? The one without an
email address or the one with the email address?

basically 1 that doesn't have any fields missing...

cheers

>
SELECT * from table where name<>"" and employeeId<>0 and email<>"";

Just a note:

Please, no double quotes for string constants, use single quotes.

Double quotes are reserved for "delimited identifiers" as defined by the
SQL Standard and supported by MS SQL Server.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||>>

Quote:

Originally Posted by

Quote:

Originally Posted by

>SELECT * from table where name<>"" and employeeId<>0 and email<>"";

>
Just a note:
>
Please, no double quotes for string constants, use single quotes.
>
Double quotes are reserved for "delimited identifiers" as defined by the
SQL Standard and supported by MS SQL Server.
>

I tend to forget those things, as its different in every programming
evironment i use...
some of the don't care, some use double quotes, and some use single
quotes...

thanks anyway for this reminder...

MS SQL compare columns to generate display name

Hello, I have the following table with 4 columns...

firstname, lastname1, lastname2, EMAIL

Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.

I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc

Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?

I hope I've explained this well :-)

Many thanks in advance for any help/advise

YasOn 17 Sep, 13:32, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello, I have the following table with 4 columns...
>
firstname, lastname1, lastname2, EMAIL
>
Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.
>
I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc
>
Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?

By the way is this even possible in MS SQL? :-)

Cheers
Yas|||Something is probably possible. Transact-SQL has very basic string
manipulation capability, and the CASE expression allows resolving to
different values depending on testable conditions. If you posted
CREATE TABLE and INSERTs for a variety of test data, along with
expected output, you might get a more specific response.

How confident are you that the email name matches the name in the
three name columns?

Roy Harvey
Beacon Falls, CT

On Mon, 17 Sep 2007 12:53:15 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

>On 17 Sep, 13:32, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

>Hello, I have the following table with 4 columns...
>>
>firstname, lastname1, lastname2, EMAIL
>>
>Table has user names and email, I would like to generate a 5th column
>called DisplayName.
>The email Id is sometimes firstname.lastname1.lastname2@. and others
>just firstname.lastname1@.
>>
>I would like to generate the display name exactly like the email eg
>firstname.lastname1.lastname2@. displayName = firstname lastname1
>lastname2.....so for james.smith display name = James Smith and for
>james.earl.smith displayName = James Earl Smith etc etc
>>
>Is there a way that I can check/compare email Id (before the @. part)
>with firstname, lastname1 and lastname2 and generate a display name
>based on what was used for the email address?

>
>
>By the way is this even possible in MS SQL? :-)
>
>Cheers
>Yas

|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

firstname, lastname1, lastname2, EMAIL
>
Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.
>
I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc
>
Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?
>
I hope I've explained this well :-)

UPDATE tbl
SET DisplayName = CASE substring(lower(email),
1, charindex('@.', email) - 1)
WHEN lower(firstname) + '.' + lower(lastname)
THEN firstname + ' ' + lastname
WHEN lower(firstname) + '.' + lower(lastname) +
'.' + lower(lastname2)
THEN firstname + ' ' + lastname + ' '
lastname2
END
WHERE DisplayName IS NULL

I have here assumed that firstname, lastname and lastname2 are entered
with proper case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Sep, 23:18, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

firstname, lastname1, lastname2, EMAIL

>

Quote:

Originally Posted by

Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.

>

Quote:

Originally Posted by

I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc

>

Quote:

Originally Posted by

Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?

>

Quote:

Originally Posted by

I hope I've explained this well :-)

>
UPDATE tbl
SET DisplayName = CASE substring(lower(email),
1, charindex('@.', email) - 1)
WHEN lower(firstname) + '.' + lower(lastname)
THEN firstname + ' ' + lastname
WHEN lower(firstname) + '.' + lower(lastname) +
'.' + lower(lastname2)
THEN firstname + ' ' + lastname + ' '
lastname2
END
WHERE DisplayName IS NULL
>
I have here assumed that firstname, lastname and lastname2 are entered
with proper case.
>

Thanks! :-)
Anyone know why I'm getting the following error when I run the above?
"Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation
conflict for equal to operation."

Its all from the same Table so strange that there would be a Collation
conflict?

Thanks|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

Anyone know why I'm getting the following error when I run the above?
"Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation
conflict for equal to operation."
>
Its all from the same Table so strange that there would be a Collation
conflict?

Collation is set by column, so it could happen. Use sp_help to review the
collations.

A possible reason that you created the table, changed the database
collation, and then added more columns to the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx