non latin characters in nvarchar

Hi NG,

is there a way to select nvarchar fields, that have non latin characters ?

Do I have to use UNICODE() and search for high numbers ?

Regards
Volker Jordan
0
Volker
6/7/2010 12:19:50 PM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
953 Views

Similar Articles

[PageSpeed] 35

Volker Jordan (v_jordan@web.de) writes:
> is there a way to select nvarchar fields, that have non latin characters ?
> 
> Do I have to use UNICODE() and search for high numbers ?
 
That or an expression with patindex:

SELECT ... 
FROM   tbl 
WHERE  patindex('%[' + nchar(nnn) + '-' + nchar(65535) + ']%', 
                col COLLATE Latin1_General_BIN2)

This assumes that there is a code point at which the Latin characters
"ends". I will have to admit that I don't know whether is such a simple
limit. It also depends on what characters you really want to find. Is
LATIN CAPIAL LETTER N WITH GRAVE (used in Pinyin) a "Latin" character
by your standards?

In any case, for these kind of patterns it is a good idea to force a 
binary collation so that the ranges works you would expect.

A possibility to explore is what support the .Net Framework offers in
this regard. If .Net Fx includes function for classification of characters,
you could write a CLR function for the task.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
6/7/2010 1:00:49 PM
Am Mon, 07 Jun 2010 13:00:49 +0000 schrieb Erland Sommarskog:

> Volker Jordan (v_jordan@web.de) writes:
>> is there a way to select nvarchar fields, that have non latin
>> characters ?
>> 
> 
> SELECT ...
> FROM   tbl
> WHERE  patindex('%[' + nchar(nnn) + '-' + nchar(65535) + ']%',
>                 col COLLATE Latin1_General_BIN2)
> 
> In any case, for these kind of patterns it is a good idea to force a
> binary collation so that the ranges works you would expect.
> 

That looks very good, but why do I have to set a binary collate ?

Regards
Volker
0
Volker
6/7/2010 2:16:13 PM
Volker Jordan (v_jordan@web.de) writes:
>> SELECT ...
>> FROM   tbl
>> WHERE  patindex('%[' + nchar(nnn) + '-' + nchar(65535) + ']%',
>>                 col COLLATE Latin1_General_BIN2)
>> 
>> In any case, for these kind of patterns it is a good idea to force a
>> binary collation so that the ranges works you would expect.
>> 
> 
> That looks very good, but why do I have to set a binary collate ?

I tried to explain that in my post, but consider this example:

   create table #data (a nvarchar(20) NOT NULL)
   go
   insert #data (a) VALUES ('RABARBER')
   insert #data (a) VALUES ('EWIGKEIT')
   insert #data (a) VALUES ('Wissenschaft')
   go
   SELECT * FROM #data WHERE a LIKE '%[a-z]%'
   SELECT * FROM #data 
   WHERE a COLLATE Latin1_General_BIN2 LIKE '%[a-z]%'
   go
   DROP TABLE #data

The desire is to find words with lowercase letters in them. But
you will find that the first select returns all three words. This is
because the range a-z relates to the collation so it goes aBbCc...z.
If you force a binary collation, you use the actual ASCII range.

To avoid such surprises, you should specify a binary collation. I 
should also add that for your Unicode quest, it is important that
you use a BIN2 collation. The BIN collations are quire weird.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/7/2010 9:39:22 PM
Am Mon, 07 Jun 2010 23:39:22 +0200 schrieb Erland Sommarskog:


> To avoid such surprises, you should specify a binary collation. I should
> also add that for your Unicode quest, it is important that you use a
> BIN2 collation. The BIN collations are quire weird.

Many thanks !
0
Volker
6/8/2010 12:46:49 PM
Reply:

Similar Artilces:

Creating a field to search and enter non duplicate data
Hello all. I have a team of 15 pople that all work with reference numbers, on occasion a number has to be sent to a different work group. I've created a spreadsheet for them to enter the numbers into, but we have been duplicating numbers. I want to create a field to enter th number into, have the field cross reference the exsting numbers, and if it does not find a match, enter the number and sort the list. I know how to create a custom macro that will accomplish what I want to do, but I'd prefer a static formula if it's possible. A formula can not do waht y...

Chart Y-axis definition. Limitation for Maximum characters ?
Hi, I encountered a strange problem (I believe every problem poser must be saying so.....) I was defining the range of y axis value in a Chart. The cells used in ranges were not together and hence in disparate places. So with Ctrl pressed I would select the cells I want. This way the name of the worksheet was also coming in the chart defining range. I had around 8 data points and hence 8 different cells. Now when selecting the cells one by one when I got to the 7th one Excel wasnt selecting it. I mean I used to select it and then put a comma and then click on the 7th cell and again a comma...

non
...

Query a non-MS/non-Active Directory LDAP Server
Hi All, Does anyone know how to query an LDAP server from Access VBA that is not a Microsoft / Active Directory (AD) server? I have found a lot of stuff about how to query AD and have succeeded in doing so. But that does not seem to transfer to the non-MS LDAP server. It gives the error "Automation error" "There is no such object on the server." Thanks, Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1 Clifford Is there a chance that there...

special character '&'
How can I use special character & inside xml fields for eConnect 7.5??? Regards Csuma ReplaceEntXMLSpecChar(sAdrsCode, "&", "&") "csuma" wrote: > How can I use special character & inside xml fields for eConnect 7.5??? > Regards > Csuma I found this in eConnect help: <VENDNAME> <![CDATA[ American Telephone & Telegraph]]> </VENDNAME> Regards Carlos Suma "Shariq" escribió: > ReplaceEntXMLSpecChar(sAdrsCode, "&", "&") > > "csuma" wrote: > > &g...

Random Characters appearing in cells
One of our users has complained about, and I have witnessed, a simple name and address spreadsheet that places the text +A1 in various cells, after the correct cell contents. This spreadsheet is not shared with anyone nor does anyone else have access to it, so that eliminates someone else messing with it. The text normally appears after she has made changes elsewhere in the spreadsheet, saves and then closes it. When she opens it again the previously mentioned mystery text appears. Any ideas?? Thank you. Ken Does the workbook have macros? Try opening it with macros disabled....

Drop Character
Is it possible to set up text formatting to automatically put a drop character on a paragraph? I have tried Modifying the Style for a paragraph and have not been able to find a way. I would like to be able to set this up so that the users of the publication do not have to go to Format, Drop Cap everytime they need to use it. Many thanks Paul Sorry this isn't available either... Might ask in the programming group. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Paul Haslam" <get_rid_of_everything_before_the_d...

Maximum number of characters in merged cells
Is there a maximum number of characters a merged cells will hold? I have a text field of merged cells however, after about 11 rows all of the text does not show even though it looks like there is room for the additional text. Bill, Do a search in help with "Specifications" and you will find: Length of cell contents (text): 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar Bernard "Bill" <anonymous@discussions.microsoft.com> wrote in message news:A2366FCA-2E93-42E3-82A5-92316657DF4C@microsoft.com... > Is there a maximu...

Non Delivery Strangeness
I rebooted Exchange 2003 SP2 and received the following NonDelivery email on 1/3/2006 for an email sent on12/15/2005. I and other users received a few of these and the server seems to perform normally for the 100's of other emails that are sent and received daily. Thanks, Dan Foxley ---------------------- Your message did not reach some or all of the intended recipients. Subject: Purchasing From Your Site Sent: 12/19/2005 2:05 PM The following recipient(s) could not be reached: smartservice@smartwool.com on 1/3/2006 10:17 AM The e-mail system was unable to deliver the message, but d...

NDR from non-existent email address
Any user that sends a meeting request to the user in question gets the following responce. Your message did not reach some or all of the intended recipients. I changed the info to the innocent. Subject: test Sent: 8/16/2005 9:01 AM The following recipient(s) could not be reached: Gay, Esse M. on 8/16/2005 9:02 AM The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ;p=XXX;l=XXXXXXX-XXXXXXX MSEXCH:MSExchangeMTA:EXCHANGE:XXXXXX This only happens with meeting invites, regular email goes through just fine. It is jus...

Non-cash, Non-regular Transactions
Money 2003 SE V11 I have a need to set up transactions, such as Mileage, to keep track of non-cash activities. I currently handle it by setting up Bills & Deposits split into two transactions totalling $0.00. The first is the Category/Subcategory I want to track and the second is a Category/(Non-Cash Offset Subcategory) that I filter out of all reports. This works ok for me. Example: (meaning 23 miles traveled) Mileage:Medical/Dental 23.00 Mileage:Offset Non-cash Transaction -23.00 However, the transaction is not regularly occurring. Yet we are required to ...

NON TAXABLE Item Setup, What TAX CODE to assign
What is the proper way to set up a non taxable item like a coupon or labor? I was assigning the <not assigned> (RMS dbase Tax ID 0) (But I just read said to delete the <not assigned> tax code. to prevent a runtime error (we have not had yet)) Do I need to setup a Non tax tax code? and add all non tax items to it? PS I have a < > (blank)(RMS Dbase tax id 3) tax code that seems to need to be deleted, RMS assigned it tax code 3. There are currently no items assigned to the < > blank tax id I can not find any guidance in the RMS Help. It seems MS ex...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

mail from non exsiting user accounts
Hi, I have one exchange 2003 cluster server. and the email address configured as user@corp.ho.com. We are not using it for external mails. one of our IBM AIX user is able to send mails to anybody in my exchange server eventhough he is not having mail account in the windows 2003 domain. if u check the ID of the sender u will see that he is also using the same domain name ie @corp.ho.com... How can i stop others who do not having account in exchange to send mails to my domain. thanks BM Uncheck "Anonymous access" from SMTP virtual server properties | Access | Authentication and r...

non project time
I am using MSP2007 for several projects en use a resource planning. The problem I have is that 1) the people have 8 hour working days but are not working 8 hours on the project. I can simply change the working time per person (it is different per group) but when I ad vacation as a task (next problem) MSP is not using the 8 hours but the adjusted time. This means that the vacation days are shorter than actual. 2) how can I use the non working time and make it visible in the Gantt Chard, like individual vacation and the non project time per day. 3) when adding vacation in the res...

Pull Data From Non-Default Calendar
Hello, I am running Outlook 2003 and I have a macro that pulls data from the default calendar. I created a new calendar named "Instructor Schedule" which is a subfolder under Calendar. I would like to change the code so that the data is pulled from the new calendar. Currently I am using the following code. Set colCal = objNS.GetDefaultFolder(olFolderCalendar).Items What change would I need to implement to get my desired results? Thanks. Regards, Chris -- Regards, Chris Set colCal = objNS.GetDefaultFolder(olFolderCalendar).Folders.Item("Instructor Sch...

Insert Automatic, Non-Updating Date Stamp
I use an Excel invoice to bill clients. Currently I have an Invoice Date field which is populated by the =TODAY() function. That's good. However, if I open the spreadsheet down the road and accidentally recalculate, the Invoice Date field is updated with the current date. That's not so good. Is there a simple macro I can use that Inserts the date when the document is created and then terminates? Something, I guess, that calls the TODAY() function and then pops the resulting date value into the appropriate cell. I know I can just use CTRL+; to insert the date manually but I'm shoo...

finding last non empty cell in a column #3
FYI - The column could be any column not just B -- cparson ----------------------------------------------------------------------- cparsons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1286 View this thread: http://www.excelforum.com/showthread.php?threadid=26238 ...

non vba way to print non continuous ranges
Hi, Is there a non vba way to print non continous ranges on the one shee using excel? Specifically, lets say I want to print data from columns A, B, C, G an K down to row 35 in each column. Is there a way I can do that withou manually making them continuous by cutting and pasting each range ont a new sheet? Regards, David Obei -- DavidObei ----------------------------------------------------------------------- DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223 View this thread: http://www.excelforum.com/showthread.php?threadid=27196 You can h...

Character counter #3
Hello, I have created a User form to allow users to enter data onto a report. To ensure that data entered does not exceed the space available I have limited the number of characters that can be entered, I want to put a box on the form that will show the user how many characters that they have available and I would like it to update as they type so they know how much space they have available. eg. 100 characters available to start, as they type this would decrease accordingly, so if they typed "Hello" the box would show 95 available. How can I do this? Thanks in advance...

Enable Non-Backorderable Items in POs (e.g. automatically cancel)
Enable identifying vendors and/or items to be non-backorderable from vendor. (e.g. automatically cancel non-received items) Many vendors do not backorder items. If an item is ordered and not shipped, it must be reordered. The current system requires the PO to be Edited so unreceived items are cancelled. This is an extra step and one if missed, is bad. For example, when an item is not received it remains in "released" status, which means the PO generator does not suggest a reorder. yet the vendor isn't going to ship. We face stock shortages and lost sales. In a busy environ...

Why the Character O disappers
Hello, When I type a sentence that starts with the letter O, then I click on bullets or numbering, the letter O disappears. This happens in Word 2007 or 2010. What is happening? T.I. Word thinks you are using O as a bullet. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "TITANI" <TITANI@discussions.microsoft.com> wrote in message news:2BE7640D-63C6-4AC8-A6BA-A9CA18992C67@microsoft.com... > Hello, > > When I type a sentence that starts with the letter O, then I click on > bullets...

Wildcard Character in an Array Formula
Does anybody know how to write an array formula that includes a wildcar character such as ? or * I'm using a simple SUM(IF) array. =SUM(IF((C6:C25="*Wiscons*"),E6:E25,0)) Ctrl+Shift+Ente -- Scorpvi ----------------------------------------------------------------------- Scorpvin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2767 View this thread: http://www.excelforum.com/showthread.php?threadid=47213 Scorpvin, try the following: =SUM(IF(ISNUMBER(FIND("Wiscons", C6:C25)), E6:E25, 0)) as array formula of course. HTH Kostis Vezerides ...

Using english and non-English Excel commands simultaneously in a non-English installation
Hi community, I like this forum very much - I got many valuable insights from it. In the past I had an English Excel installation at the office available, recently my employer moved to the German version and deinstalled the english one. Now, when i use a english command like =VLOOKUP(A1,'Sheet 2'! A1:H200,8,FALSE) adopting the country settings for "," with ";" Excel comes up with #NAME? since I used the english term, not the german one - but sometimes the german equivalent is not at hand as fast as needed ;-) However if a open a english coined workbook of a US/UK c...

How do I count non blank rows only?
This seems so simple but I've spent hours without resolution. In column A, I need to count the number of rows that actually have data in them. If there is no data, I need to keep column A blank. Here's an oversimplied example of my simple list. 1 John Doe 2 Susan Smith 3 Joe Dear In column A now I have =COUNTA($B$2:B64) and that works great until you hit a row with no data. I have tried all these: =IF(G69>0,(=COUNTA($B$2:B69))," ") Also =COUNTIF($B$2:B69,">0") There's got to be an easy way that I'm missing!! HEEE...