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
972 Views

Similar Articles

[PageSpeed] 35

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:

underscore sign in query
Hello folks, I am trying to exclude record from a query that start with a letter followed by the underscore and other letters. Example: B_test B_test2 B_Hello B_Mach B_yellow BA BT In this example, I need to exclude anything that starts with B_ My understanding is to use the NOT Like function in that colum criteria such as: Like 'B[_]%' However, it does not do anything (I still get all the records). I know that underscore is in SQL is not being considered laterally. So how do I handle this? Thank you, Silvio This worked for me: Not Like "B_&...

I can not sort hyperlink field in the query
Hello My query is like below, hyperlink field will not be sort in data sheet view.out put data is like below: Please help. thanks in advanced. Unit No Line Size Fluid Code Unit No Iso Pipe Sequence No Piping Class Insulation Code hyper 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_3.pdf 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_4.pdf 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_1.pdf 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_2.pdf my query: SELECT DISTINCTROW qryiso.[Unit No], qryiso.[Line Size], qryi...

SQL insert query help needed
Hi all I am new to SQL and i would to know if anyone can help me work around this problem: i have 2 tables table1 (column1, column2, column3, column4) where column1 = integer - auto increment, clumn2 = text, column3 = text , column 4 = integer and table2 (column1 , column2) where column1 = integer - auto increment and column2 = text Now table1.column4 is related to table2.column1 thats table1.column4 = table2. column1 what i was trying is to be have an SQL statement that can insert values that are not fixed maybe different every time but i want them to be added into ...

Query Builder In the Instant Search pane is not working
Hello, The Query Builder In the Instant Search pane within outlook2007 is not working. I'm unable to do advanced search due to this. Please help! Do you have Windows Desktop Search installed? For details see; http://www.msoutlook.info/question/269 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "kevind" <kevind@discussions.microsoft.com> wrote in message news:AA921A...

VB
I have a query which displays contents in a grid. The SourceObject Property is "Query.qry-ShowData". How can I return the actual SQL statement used by this query? The reason is this query pulls contents from different forms to create the WHERE statement and I would like to see the actual SQL statement used by the "Query.qry-ShowData". Thank you in advance for your help. Mike Hi, try: debug.print currentdb.querydefs("qry-ShowData").SQL -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "Michael Kintne...

SQL query for items on hand and sold
Help me with a query for eash item qty on hand and sold in one statement? I know on hand comes from item.quantity and sold qty comes from transactionentry.quantity but I don't know how to show them together in a report. This is a multi-part message in MIME format. ------=_NextPart_000_009A_01CA3853.0089EAC0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Sammm, Do you realize the the OnHand is an available column on the Detailed = Sales Report? After running the report, right click on the body of the = report | Show/Hide Columns | and...

Parameter Query
Hi All, Here's what I have: TABLE1(tbl_MAIN_DISC) with the following fields: DISC_ID=PK DATE USER_ID USER DISC_TYPE_ID DISC_TYPE TABLE2(tbl_DISC_TYPE) with the following fields: DISC_TYPE_ID=PK DISC_TYPE The relationship I have is a 1 (tbl_DISC_TYPE) to many (tbl_MAIN_DISC). I created a form with two combo box that populate two txt fields(USER_ID & DISC_TYPE_ID). I created a parameter query based on the txt fields from the form and it works great on pulling that specific USER_ID and DISC_TYPE_ID Is there a way to use the same form and only select the USER_I...

Outputting multiple query results as VB variables
I have a form that show results from a query that I need to use to print multiple pdfs. The query usually has multiple results and I need to print specific pdf files based on those results. For example, if the query returns A, B, and C, I need to print A.pdf, B.pdf, and C.pdf. I can print the files with VB code once I get the results as variables, but I don't know how to convert the multiple query results as multiple variables. I hope that makes sense. I am relatively new to access and especially to VB so any help (or alternate way to accomplish what I am doing) would be appreciated. ...

Summing Two Query Fields
I am trying to sum to fields from two separate queries. I have tried adding them together (you know, [field1]+[field2] as well as [table1]! [field1]+[table2]+[field2]) but that isn't working. Instead I am getting two rows of data (one is field1 and the other is actually the sum). Any ideas? Thanks so much! On Jun 27, 3:03 pm, Christie <christiebl...@gmail.com> wrote: > I am trying to sum to fields from two separate queries. I have tried > adding them together (you know, [field1]+[field2] as well as [table1]! > [field1]+[table2]+[field2]) but that isn't working. Instead...

Make Table Query 01-04-08
I am trying to post new data to a table every week and apply an autonumber to rank the data. How can I apply an autonumber to the new data after my delete query has cleared out the previous data? Automatically, of course. I would like my macro to be able to delete and post the new data by rank, so that reports can be generated as well. Adam Perhaps you and I don't share the same definition of "rank". To me, this term implies some inherent order. Is this how you use the term? What will having a "rank" for each record allow you to do that you couldn't ot...

Need query to find for matches of one field within another field
I'm looking for a way to find the field from one table within the field of another table. For example: Table1 => Field1 = Group1 Table2 => Field1 = Group1, Group2, ... , Groupn The query would allow me to compare Table1.Field1 with Table2.Field1 and see that "Group1" is within it. I'm used to doing this with the LIKE command using specific text and a wildcard (i.e. '%group1%) but not using fields. Any suggestions on how I can accomplish this? On Tue, 18 Mar 2008 14:15:29 -0700 (PDT), kelly.salvatori@gmail.com wrote: >I'm looking for a way to find the field...

Business Portal Query for KPI of 3rd Party Tables or Database
I would like to add a suggestion that from the Business Portal Query the user should be able to make queries of databases other than Great Plains whether it is in the same server or another server of GP so it can be used in the Business Portal KPI application, doing so will eliminate the clients that they have to look for other KPI solutions along with the BP KPI in order to build their complete KPI structure for their Business applications, at the moment if there is a need to use a KPI system for an organization they cant ONLY depend on the BP KPI as this feature is not available ----...

Query to pull set criteria or all
I have the following query: SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682 Countsheet].Product, [P682 Countsheet].[Assign Date], [P682 Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682 Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682 Countsheet].Task = [Area to Task Query].[Task ] WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682 Countsheet].Status)<>"Completed")); I would like the query to ask for an OPID as i have set o...

Queries to lookup a value
I have a situation where what I want to do is: Combo Box one [Field 1] shows all the options e.g.: (Fruit, Meat, Breaed) Combo Box two [Field 2] shows all the options that have been limited by [Field One] e.g.: (Apples, Pears, Oranges) Combo Box three [Field 3] shows all the options that remain after filtering by Field1 and Field2 Once I have that I want to have a form field that Looks up [Field4] and puts it in that form field. This is to be able to say give me fruit, apples, fuji, and then put in the unit price field the correct cost. I could really use help on this!!! Thanks Searc...

what is the query to list records with a certain field empty?
what is the query to list records with a certain field empty? Put Is Null in the criteria row of the query -- Wayne Manchester, England. "aa" wrote: > what is the query to list records with a certain field empty? > > > Thanks a lot ...

Suppress promt in Update Query
I have a macro set up that runs an update query. (macro attached to a button) I am wondering if there is a way to stop the two dialouge boxes that come up when the query is run. I don't want to confuse the users. Not a big deal if it's not possible. I will just tell them to click yes/Ok On Tue, 22 May 2007 13:25:02 -0700, cprav wrote: > I have a macro set up that runs an update query. (macro attached to a button) > > I am wondering if there is a way to stop the two dialouge boxes that come up > when the query is run. I don't want to confuse the users. > ...

Use Excel to make an update query to another database table?
I have a spreadsheet in Excel with information on it that I'd like to update to a database table (outside of Excel). I have been using Excel to pull database information in, and now I'd like to use it to write data to the same table. To this point I've just been copying the data into an Access table and creating an update query. Anyway to get Excel to do this?? Many thanks in advance. ...

query for current winning streak
Hi I am trying to run a query to show what a player's current and winning streak is. I was told to use this query to start. This gives the player and the last game he lost: SELECT playerID, MAX(gameID) AS mgame FROM myData WHERE NOT isWin GROUP BY playerID ---------------------------------------- saved as qlatestLost Then use the below query to give the number of games after the last loss ------------------------------- SELECT playerID, COUNT(qlatestLost.PlayerID) AS actualWinStreak FROM myDataLEFT JOIN qlatestLost ON myData.playerID = qlatestLost.playerID ...

Update Query
Hello, For every record in a table I need to update one of four Yes/No check box fields based upon date span. If the "In Date" is in the same month as "Serve D" or "Return D" field place a check in the "DispodWithinSameMonth" yes/no field. If the "In Date" is within 30 days of the "Serve D" or "Return D" field place a check in the "DispodWithin30Days" yes/no field. If the "In Date" is within 60 days of the "Serve D" or "Return D" field place a check in the "Dis...

Updateable Query
Why can't access just read my mind. It would be easier. I have 2 table tblStudents-Stores Name, ID#s, Ethnicity, Etc tblTests - Stores which particular tests the students should take Math, Reading, Science Etc. Currently tblTests is empty. I would like to set up a query that links the tblStudents with tblTests via the LocID number with all records from tblStudent showing. LocIDis the primary key for both tables. When I tried to link them it makes the query uneditable. I looked on the site and tried to include all the fields from tblTests and only last and first name ...

Paramter Update to query
I have a table called 'temp_table' this table has a column named 'month_year' and I would like to know how can I create a query where I can just update that table with the month and year. So I want to be able to click on the query and it will ask me to put in the month and year and it will automatically update that field in that table. Can this be done and how? On Wed, 9 Jan 2008 12:15:01 -0800, jac007 <jac007@discussions.microsoft.com> wrote: >I have a table called 'temp_table' this table has a column named 'month_year' >and I would like to ...

Open Query Design In SQL view
Is it possible to open the query design window directly to the SQL View in existing queries or in creating new queries? Presently it is two or three clicks to get there. If the existing query was saved in SQL view, opening it subsequently in Design view should open it in the SQL view. I'm not aware of any way to have new queries automatically open in SQL view. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tcb" <tbenson@mn.rr.com> wrote in message news:1190637257.981140.239390@19g2000hsx.googlegroups.com... > Is it possible t...

Excel should allow independent zoom adjustment of split panes
I use the split pane feature of Excel, but I feel it could be improved if one could independently adjust the zoom of each pane. One of the panes could then be used as a sort of outline at fractional magnification, while the other pane could be used to modify individual cell content at higher magnification. Hi if you'ld like to let MS know - send them an email at mswish@microsoft.com with Excel in the subject line. cheers JulieD "One Excel User" <One Excel User@discussions.microsoft.com> wrote in message news:E3588DE6-D474-4DFB-B2C9-5D31BDABCF00@microsoft.com... ...

design query grid match anywhere question
In the design query grid I want to match the letters "ABC" anywhere in the column 'description'. I could not get instr to work by using: InStr(description, "ABC")) > 0 How else can I match ABC (upper and lowercase) to anywhere in my 'description' column? Thanks! On May 24, 2:04 pm, Nooby <stwil...@writeme.com> wrote: > In the design query grid I want to match the letters "ABC" anywhere in > the > column 'description'. I could not get instr to work by using: > InStr(description, "ABC")) > 0 > > How ...

Query table not updating as expected
Hi, I have a table concerning Lab Tests which I present using a form. One of the fields in this table is called Test Status where the user can select the status via a combo box. Under this combo I have a label box which shows a summary of the status for the entire table e.g. OK: 10, NOT_OK: 1, NOT_TESTED: 5. When the combo box is changed, I issue a function to change this label. This function does something like this - Private Sub UpdateLabelTestScriptStatus() Dim recordSetTestSuiteStatus As DAO.Recordset Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select * from qryT...