Queries independent of accents?

Hello!

We have a DB of people from many countries. Some names contain chars with 
accents like � or � and other types of accents. Is there a way of searching 
all names in the DB containing any kind of accent without specifiying the 
acctents used? Is there a function like f("�") = "e" where f is the function 
I look for.

Thx in advance!

Fritz 

0
Fritz
1/25/2010 11:31:54 AM
sqlserver.server 1327 articles. 0 followers. Follow

8 Replies
978 Views

Similar Articles

[PageSpeed] 29

Fritz
Do you store the data as NAVARCHAR(n) datatype? Have you specify N  just 
before a value in WHERE condition?

Hmm, I think may want to take a look into REPLACE function to remove accents





"Fritz Franz" <fritzfranz24@hotmail.com> wrote in message 
news:4b5d8160$0$6574$9b4e6d93@newsspool3.arcor-online.net...
> Hello!
>
> We have a DB of people from many countries. Some names contain chars with 
> accents like � or � and other types of accents. Is there a way of 
> searching all names in the DB containing any kind of accent without 
> specifiying the acctents used? Is there a function like f("�") = "e" where 
> f is the function I look for.
>
> Thx in advance!
>
> Fritz 


0
Uri
1/25/2010 1:16:19 PM
"Uri Dimant" <urid@iscar.co.il> wrote

>> We have a DB of people from many countries. Some names contain chars with 
>> accents like � or � and other types of accents. Is there a way of 
>> searching all names in the DB containing any kind of accent without 
>> specifiying the acctents used? Is there a function like f("�") = "e" 
>> where f is the function I look for.

> Fritz
> Do you store the data as NAVARCHAR(n) datatype? Have you specify N  just 
> before a value in WHERE condition?
>
> Hmm, I think may want to take a look into REPLACE function to remove 
> accents

Thanks, Uri for your answer!
Until now the fields are stored (SQL 2000, soon SQL 2008) as type VARCHAR. 
Why do you ask?

Well, using REPLACE I would have to deliver a complete list of accents I 
want to replace This would be a little bit rigid. But if there is no other 
solution ...

Thx again, Fritz

0
Fritz
1/25/2010 2:49:29 PM
This is a collation issue.  Apparently your database is using an "accent 
sensitive" collation.  If that is not what you want, you would need to 
recreate the database in the proper collation, then copy all the data into 
the newly created database.   If you only need to tweak some queries, you 
can use:

SELECT ...
FROM table_with_accents
WHERE search_column COLLATE SQL_Latin1_General_Cp1_CI_AI_KI_WI = 'esteban'

The COLLATE (in this case) is the function that removes the accents from 
consideration.  Note, however, that changing the COLLATE on the search 
column means that an index on that column cannot be used for index seeks, 
which will impact performance to some extent.

RLF

"Fritz Franz" <fritzfranz24@hotmail.com> wrote in message 
news:4b5dafb1$0$6565$9b4e6d93@newsspool4.arcor-online.net...
> "Uri Dimant" <urid@iscar.co.il> wrote
>
>>> We have a DB of people from many countries. Some names contain chars 
>>> with accents like � or � and other types of accents. Is there a way of 
>>> searching all names in the DB containing any kind of accent without 
>>> specifiying the acctents used? Is there a function like f("�") = "e" 
>>> where f is the function I look for.
>
>> Fritz
>> Do you store the data as NAVARCHAR(n) datatype? Have you specify N  just 
>> before a value in WHERE condition?
>>
>> Hmm, I think may want to take a look into REPLACE function to remove 
>> accents
>
> Thanks, Uri for your answer!
> Until now the fields are stored (SQL 2000, soon SQL 2008) as type VARCHAR. 
> Why do you ask?
>
> Well, using REPLACE I would have to deliver a complete list of accents I 
> want to replace This would be a little bit rigid. But if there is no other 
> solution ...
>
> Thx again, Fritz
> 


0
Russell
1/25/2010 3:47:54 PM
You can use COLLATE to specify accent independent collation:

CREATE TABLE Foo (
  keycol INT NOT NULL PRIMARY KEY,
  datacol NVARCHAR(30));

INSERT INTO Foo VALUES(1, N'�');

SELECT datacol
FROM Foo
WHERE datacol = N'e';

SELECT datacol
FROM Foo
WHERE datacol = N'e' COLLATE SQL_Latin1_General_CP1_CI_AI;

/*

(1 row(s) affected)
datacol
------------------------------

(0 row(s) affected)

datacol
------------------------------
�

(1 row(s) affected)

*/


-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/25/2010 3:50:54 PM
Oops, sorry.  I used a 2008 collation.  Try:

SELECT ...
FROM table_with_accents
WHERE search_column COLLATE SQL_Latin1_General_Cp1_CI_AI = 'esteban'

RLF


"Russell Fields" <RussellFields@nomail.com> wrote in message 
news:O0TZDXdnKHA.5464@TK2MSFTNGP02.phx.gbl...
> This is a collation issue.  Apparently your database is using an "accent 
> sensitive" collation.  If that is not what you want, you would need to 
> recreate the database in the proper collation, then copy all the data into 
> the newly created database.   If you only need to tweak some queries, you 
> can use:
>
> SELECT ...
> FROM table_with_accents
> WHERE search_column COLLATE SQL_Latin1_General_Cp1_CI_AI_KI_WI = 'esteban'
>
> The COLLATE (in this case) is the function that removes the accents from 
> consideration.  Note, however, that changing the COLLATE on the search 
> column means that an index on that column cannot be used for index seeks, 
> which will impact performance to some extent.
>
> RLF
>
> "Fritz Franz" <fritzfranz24@hotmail.com> wrote in message 
> news:4b5dafb1$0$6565$9b4e6d93@newsspool4.arcor-online.net...
>> "Uri Dimant" <urid@iscar.co.il> wrote
>>
>>>> We have a DB of people from many countries. Some names contain chars 
>>>> with accents like � or � and other types of accents. Is there a way of 
>>>> searching all names in the DB containing any kind of accent without 
>>>> specifiying the acctents used? Is there a function like f("�") = "e" 
>>>> where f is the function I look for.
>>
>>> Fritz
>>> Do you store the data as NAVARCHAR(n) datatype? Have you specify N  just 
>>> before a value in WHERE condition?
>>>
>>> Hmm, I think may want to take a look into REPLACE function to remove 
>>> accents
>>
>> Thanks, Uri for your answer!
>> Until now the fields are stored (SQL 2000, soon SQL 2008) as type 
>> VARCHAR. Why do you ask?
>>
>> Well, using REPLACE I would have to deliver a complete list of accents I 
>> want to replace This would be a little bit rigid. But if there is no 
>> other solution ...
>>
>> Thx again, Fritz
>>
>
> 


0
Russell
1/25/2010 4:24:29 PM
Thanks to all of you! 
Now I discovered the COLLATION clause and applied it. It works very fine.

Fritz
0
Fritz
1/25/2010 10:14:06 PM
Hi Russel
> The COLLATE (in this case) is the function that removes the accents from 
> consideration.


Hmm, did not know that, thanks for pointing it out



"Russell Fields" <RussellFields@nomail.com> wrote in message 
news:O0TZDXdnKHA.5464@TK2MSFTNGP02.phx.gbl...
> This is a collation issue.  Apparently your database is using an "accent 
> sensitive" collation.  If that is not what you want, you would need to 
> recreate the database in the proper collation, then copy all the data into 
> the newly created database.   If you only need to tweak some queries, you 
> can use:
>
> SELECT ...
> FROM table_with_accents
> WHERE search_column COLLATE SQL_Latin1_General_Cp1_CI_AI_KI_WI = 'esteban'
>
> The COLLATE (in this case) is the function that removes the accents from 
> consideration.  Note, however, that changing the COLLATE on the search 
> column means that an index on that column cannot be used for index seeks, 
> which will impact performance to some extent.
>
> RLF
>
> "Fritz Franz" <fritzfranz24@hotmail.com> wrote in message 
> news:4b5dafb1$0$6565$9b4e6d93@newsspool4.arcor-online.net...
>> "Uri Dimant" <urid@iscar.co.il> wrote
>>
>>>> We have a DB of people from many countries. Some names contain chars 
>>>> with accents like � or � and other types of accents. Is there a way of 
>>>> searching all names in the DB containing any kind of accent without 
>>>> specifiying the acctents used? Is there a function like f("�") = "e" 
>>>> where f is the function I look for.
>>
>>> Fritz
>>> Do you store the data as NAVARCHAR(n) datatype? Have you specify N  just 
>>> before a value in WHERE condition?
>>>
>>> Hmm, I think may want to take a look into REPLACE function to remove 
>>> accents
>>
>> Thanks, Uri for your answer!
>> Until now the fields are stored (SQL 2000, soon SQL 2008) as type 
>> VARCHAR. Why do you ask?
>>
>> Well, using REPLACE I would have to deliver a complete list of accents I 
>> want to replace This would be a little bit rigid. But if there is no 
>> other solution ...
>>
>> Thx again, Fritz
>>
>
> 


0
Uri
1/26/2010 9:16:42 AM
Just a note for the sake of some later reader:

The COLLATE (with an Accent Insensitive collation) is the function that 
removes the accents from consideration.

RLF

"Uri Dimant" <urid@iscar.co.il> wrote in message 
news:u8rFogmnKHA.4628@TK2MSFTNGP06.phx.gbl...
> Hi Russel
>> The COLLATE (in this case) is the function that removes the accents from 
>> consideration.
>
>
> Hmm, did not know that, thanks for pointing it out
>
>
>


0
Russell
1/26/2010 4:05:34 PM
Reply:

Similar Artilces:

Simple calc query
I am new to Excel and have Excel 2007 if that makes any difference. I am just trying to write my own vehicle logbook for my own vehicle which is used personally and for work. To denote this I have done the following: value 1 value2 Notation work column Personal column The above are different cells. Value 1 is the start mileage. Value2 is the end mileage. Notation is either P or W. Work Column has the formula =IF(Ex="P",Dx-Cx,) where the "x" equals the cell number (eg, E6 or whatever) and of course it goes for rows from A downwards. The end result, so far, is that...

Database with only query relationships
I have been asked if there are any functional problems with a database having only relationships defined by queries. Will it be stable? Will there be issues and if so of what nature? Thanks! On Wed, 23 May 2007 12:48:03 -0700, Cheese <Cheese@discussions.microsoft.com> wrote: >I have been asked if there are any functional problems with a database having >only relationships defined by queries. Will it be stable? Will there be >issues and if so of what nature? Thanks! If there are no relationships defined between the Tables, then relational integrity will *not* be enfo...

need help with query ...
Hi, I am looking to write a query with multiple factors and do not know the best way to do it. I want use Event Type, Status, and Region as my queries variables. By default I want show "all", but then be able to use drop downs to filter with. Can anyone please give me an idea on the best way to do this? If this does not seem to make sense, please let me know. Kind Regards, Ray To be able to have combo boxes for query parameters, you must create a form with an unbound combo box for each parameter. See http://www.mvps.org/access/forms/frm0043.htm at "The Access Web" ...

Number of query values and destination fields are not the same.
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same. I am getting this error when i insert the data through JDBC You are probably appending records to a certain table. In this case you might have selected 10 fields to append when there are only 9 fields in the receiving tabel or something like that. Go at it field by field. The field that i want to append does it exist in the receiving table etc... do this one by one and you'll see the one that's missing. -- Maurice Ausum "thej" wrote: > [Mic...

Update/Append Queries minus confirmation Dialouge
Does anyone know how to silence the confirmation dialogue appear when you run an update or append query. The first message box asks for confirmation before you run and update table records. The second shows the number of records that will be updated. I'm trying to automate an application with various queries and macros and need to eliminate these message boxes from the end-user. Thanks for the help! News, In a macro, you just need to put a SetWarnings/No action before the first OpenQuery action. -- Steve Schapel, Microsoft Access MVP news.newsguy.com wrote: > Does anyo...

Query-Based DL with email addresses that are external to exchange.
Exchange 2003 Enterprisse with SP2, running on Windows 2003 Server Enterprise with SP1. I am trying to setup a Query-based Distribution List that will allow us to send to our students. The student accounts are in AD, but they do not have Exchange mailboxes, their mail goes to another non-exchange system (IMail Secure). The QBDL matches on the email field on the users account properties. The QBDL does find all the email addresses that we want to send mail to. The problem is that when we send a message to the QBDL (from either Outlook 2003, Outlook 2007, or from the OWA) the message tha...

Dcount: referencing a saved query field in a mathematical expression
Hi all, Quick question regarding referencing a saved query field in a mathematical expression for a dcount function. I have a saved query called GetTmpTotPayoutFSR with the following code: SELECT payee_id, period_id, manager_flg, financial_center, cp_spread AS spread, annual_goal FROM WORK_TOT_PAYOUT WHERE manager_flg=0; -------------------- This saved query is referenced in the following update statement that utilizes a dcount: UPDATE WORK_INDV_MGR_BUMP AS IMB SET tier_1_count = dcount("[payee_id]","[GetTmpTotPayoutFSR]","financial_center = '"...

Query leaving out some data
Access 2003 I think this must be a pretty easy question, but unfortunately I'm unable to identify the solution . . . SIMPLIFIED SUMMARY Customer Table CustomerID Company Name 3301 ABC Industries 3302 My Store, Inc. 3304 A1 Company 3305 Mom & Pop's Shop Delivery Table CustomerID OrderID 3301 988374 3302 788362 3303 700287 3304 679028 Query should pull CustomerIDs for all Orders and list the CustomerID, OrderID and Company Name. (SQL = SELECT Deliveries.CustomerID, Cu...

Dont need to use query and indexing feature of moss in project ser
we are going to deploy sharepoint portal server 2007 in existing environment of project server 2007. which is two server installation of project server 2007 . In which first is application server and second is database server. Is it necessary that we start Sharepoint server search and use Query and Indexing feature in front end application server of project server 2007 after installation of MOSS 2007 in front end server ? Because I need only sharepoint server 2007 in existing enviornment of project server 2007 for proposal review workflow of...

Show queries
How do I show all queries that a attached to a spreadsheet? Varies a little depending on version, but if you go to Data>'Get External Data'>'command referring to queries' it will take you to a navigator window that lists the queries or a dialog box with a Queries tab. (The content in single quotes will vary with Excel version.) HTH |:>) "Brett" wrote: > How do I show all queries that a attached to a spreadsheet? ...

Querying a list with Excel
I am starting out with a list like Tech Date Code Actual Billed DON 11/15/2004 E 5.40 6.00 DON 11/15/2004 W 3.00 4.00 DON 11/15/2004 E 4.00 4.00 DON 11/15/2004 DO 0.30 4.00 DON 11/15/2004 HD 8.00 0.00 Paul 11/15/2004 E 3.00 4.00 Paul 11/15/2004 E 2.30 2.00 Paul 11/15/2004 E 0.40 0.50 Paul 11/15/2004 E 0.60 0.50 Paul 11/15/2004 E 3.00 3.00 Paul 11/16/2004 W 3.00 0.90 Paul 11/16/2004 CV 2.00 2.30 Paul 11/16/2004 CV 2.40 2.00 Fred 11/15/2004 PDI 3.50 4.50 Fred 11/15/2004 W 0.30 0.20 Fred 11/15/2004 W 0.10 0.20 Fred 11/15/2004 W 2.30 3.00 Fred 11/16/2004 LD 2.00 1.50 Paul 11/16/2004 LD 3....

Datasheet Subform Query
I have a Datasheet subform for which I have a field name CalendarYear which basically lets me know that fees were received for that Calendar Year. I have entered information for the 2009 Calendar Year and some information for the 2010 Calendar Year. I am trying to get my query to give me all records for which fees have not been received for the 2009 and 2010 Calendar Years. I tried the NULL function but that isn't working or maybe I am not using it correctly. I placed IS NULL in the criteria for Calendar Year. Can this not be done in a datasheet? Thanks in advance for your assi...

Query help 11-27-07
I'm trying to create a query that will calculate how much someone has contributed year-to-date to their supplemental health care plan. Basically the employee agrees to contribute an amount of their choosing. This amount is then divided out so that the individual pays a certain portion of that out of each bi-weekly paycheck. The part that is causing me headaches is that we want to stop calculating a running total for those employees who terminate, and instead calculate their contributions only up to their termination date. Here's what I have so far: This is what I had origin...

can't rename field in MS Query?
I'm using Microsoft Query to bring in some data. I have the query in SQL so i can do some groupings and count values in one of the fields. My problem is I'm having difficulty renaming a field, so it comes back with no name at all. The SQL line is: SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus The count comes back properly, but the changed field name (i.e., CountStatus) doesn't appear. The field name is blank. How does this need to be written so the field name comes back as CountStatus? Thanks rachael ...

Total Within A Query
Hi, I have the following query the gives me the total Balance by pool. I'm Trying divide the Sum of each pool to the over all balance within a query. SELECT POOL, Sum(Portfolio.Balance) AS SumOfBalance FROM Portfolio GROUP BY Portfolio.POOL Example: Pool Balance % 2006-01 10 67% 2006-02 5 33% Please advice Thanks One way to approach this: SELECT Pool, Sum(Balance) as SumOfBalance, SumOfBalance/DSUM("Balance", "Portfolio") as Pct FROM Portfolio GROUP BY PortFolio Another that would work is the following. I believe this would be faster with a lar...

Query based on field in form, not working in 2007
Hi After upgrading to Access 2007 (from 2003), I have a query that has stopped working. It is something like SECELT id FROM [tbl names] WHERE ([tbl names].firstname = [forms]![My form][firstname]); It does not seem to register the value of the "firstname" textbox in the form [My Form]. Accordign to the SQL profiler, the query just sends an empty field "". Therefore instead of searching for a specific entry, I get all my entries... The query seemed to work fine in Access 2002 and 2003. When using WHERE ([tbl names].firstname LIKE "*" & [forms]![My for...

I need to query the field name of "true" value out of 3 fields
On a dental screening form for kindergardeners, I have 3 fields (Class I Cavities, Class II Cavities, Class III Cavities) with True/False datatype (check box on form). In any given record, all three fields may be False (no cavaties...) however no more than one can be True, (when cavaties are present, are you with me?) My question is this. How can I set up a query that tells me only the one field name where value is "True", so I can build it into a report for each kid? I didn't design this database. If I had I would have made one field called "Severity", with ...

Help...Query Results Posted on a Form
I have created a form w/ almost all the fields from one table. I have three particular fields that the users will input data, costs, into. I want the fourth field to populate w/ the results of those three and then store itself w/ the info the user input into the form. I have created a query which will calculate that cost. I have attempted to add it to the form with a combo/list box and added a button to run the query for the user. I get the results but it's all the results of each record I create versus just one. I appreciate any help or insights.. Thank you Start by basing your form...

Query to count time range
I need to count the number of calls between a time range. Data would need to look like this: Date Time 1/10/08 07:38 1/11/08 07:15 1/12/08 08:47 Time # of calls 07:00-07:59 2 08:00-08:59 1 SELECT DatePart("h",[TableName].[Time]) as HourNumber , Count([TableName].[Time]) as NumCalls FROM [TableName] GROUP BY DatePart("h",[TableName].[Time]) In the query grid -- add your table -- add the time field twice -- Modify the first to read Field: HourNumber: DatePart("h",[Time]) ...

How to run a 'total' query
Hi,I'm relatively new to access and Im using access 2003. I created asmall database in an effort to track how much my friends owe me (justas a learning experience). I have two tables. 'Debts' and 'Debtors'The primary key in 'debtors' is used in 'debts' as the refference.In 'debts' I enter data in this format:Debtor ID (Primary Key from 'debtors')ReasonDateDueAmountPaid (checkbox y/n)On time (checkbox y/n)In the 'debtor' database I want a field for each debtor called 'TotalDebt'. How can I add up all the 'Amount Due' ...

Query or code?
I have two fields: Category and NumberOfContracts. Category takes 3 values, call them A, B, and C. For each record there are a number of Contracts associated with the choice of Category. So my records look like: Category NumberOfContracts B 3 A 1 B 2 C 5 C 3 There are other fields as well, of course. What I want is to sum the number of contracts for each Category. I want to generate a report that looks something like: Count of All Contracts: 14 Total of A: 1 Total of B: 5 Total of C: 8 Can I do this using a q...

Cross-Tab Query Question
I have a report that uses a cross-tab query. I had to set the column headings in order to get the query to display in the report. This is a report that is put out monthly and will only have details for 6 months worth of information at most. Once created, this report will get handed off to another person to update and distribute. Is it possible to get the cross-tab query to display without having to manually type the months into the ColumnHeader property? Essentially, short of writing code, is there a way to automate that process? Sorry. Figured it out from an earlier post. Rerea...

Adding filter to query
I'm trying to export to excel the filtered results of an underlying query on a split form. In the on click event of a command button I create a querydef and then use transfer spreadsheet to create an Excel workbook. The code creates the spreadsheet, but it includes all the records. I need to make it create only the filtered records. I've tried several methods, but can't seem to figure this out. Any help will be greatly appreciated. TIA, Ken I just answered a question just like this yesterday. I got a green check for it! Maybe I will get another today... ...

Calculation In Query
I am new to Access, and am unsure how to run a calculation based off data in my table using a query. I have 2 multi-column lookup fields in my table and want to divide one by the other. I have a feeling that Access is trying to use the data from the other column in the multi-column lookup. How do I make certain that Access is only looking at the data that shows in the field to perform the calculation? For instance, the multi-column looks up 1234 in the first column of the drop-down and has 5678 in the second portion. When you click it 1234 shows in the field, this is what I want it to d...

dynamically choose columns in query
I'm trying to create a query such that when the user runs it, the proper columns (fields appear). We have 12 columns, one for each month of our fiscal year (which goes from July to June). Basically, we run the query once a month. When we run it, for example in April, the months prior to April & including April (this would be July thru April), will have $0. The months after April (May and June) will have dollars. When we run this query in April, we'd rather not see each of the columns for July thru April because all the records have all $0 in those columns. Sea...