case insensitive search pri unicode

12 отговора 288 прегледа
#1

case insensitive search pri unicode

imam si edna baza na SQL server 2000, koqto sydyrja informaciqta za edin multiezichen sait.
za celta na zanqtieto multiezichnost sme vkarali informaciqta v UNICODE.

vaprosa e kak da napravq case insensitive search?

ako vytreshnoto predstavqne na "c" e "СЃ" a na "C" e "РЎ"
kak da go nakaram da razbere che "РЎ" e golqmata bukva na "СЃ"?

mersi ako nqkoi znae nqkakvo reshenie (kato izkljuchim minavaneto na win1251 edcoding :)) shte sym mnogo priznatelen
mersi

mamma mia
Отговор
в отговор на naskovoto #2

Otvori si SQL Server ->bazata danni,

klikni s desnia klavish na mouse -> Properties

W tab poletata razgledai smianata na vytreshnia format na bazata danni. Ima for example utf-8 case-sensitive, case-insensitive i oste nad 40 variacii na tema razlichni kulturi i ezici. V dokumentaciata na SQL Server 2000 e opisano podrobno vsiaka edna kodova podmiana kakvo sydyrja i izkluchva.

Отговор
в отговор на Long Runner #3

Re: Otvori si SQL Server ->bazata danni,

:(( ne namiram takyv tab
ima:
General, Data Files, Transaction Log, Filegroups, Options, Permissions
tova sa tabovete na properties na bazata.

a dali trqbva da sa vyv format nvarchar i ntext za da bachkat tezi neshta?

mersi za nasochvaneto btw :)

mamma mia
Отговор
в отговор на naskovoto #4

V Unicode

V Unicode vinagi triabva da izpolzvah nvarchar, ntext, tam niama varchar, text.

Sled niakolko chasa ako ne zabravia :_)) ste hvyrlia pogled na SQL Server 2000 zastoto i az imah razni podobni problemi predi vreme i taka gi opravih za broeni minuti.

Отговор
в отговор на naskovoto #5

Стъпка 1:

ЗНачи имаш доста работа, да ти кажа. Първо виж това и си избери подходящата система, за да нямаш шроб;еми после:
-----------------------------
SQL collation name
30 Binary order, for use with the 437 (U.S. English) character set.
31 Dictionary order, case-sensitive, for use with the 437 (U.S. English) character set.
32 Dictionary order, case-insensitive, for use with the 437 (U.S. English) character set.
33 Dictionary order, case-insensitive, uppercase preference, for use with the 437 (U.S. English) character set.
34 Dictionary order, case-insensitive, accent-insensitive, for use with the 437 (U.S. English) character set.
40 Binary order, for use with the 850 (Multilingual) character set.
41 Dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
42 Dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
43 Dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
44 Dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set.
49 Strict compatibility with version 1.x case-insensitive databases, for use with the 850 (Multilingual) character set.
50 Binary order for use with 1252 character set.
51 Dictionary order, case-sensitive, for use with 1252 character set.
52 Dictionary order, case-insensitive, for use with 1252 character set.
53 Dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
54 Dictionary order, case-insensitive, accent-insensitive, for use with 1252 character set.
55 Alternate dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
56 Alternate dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
57 Alternate dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set.
58 Scandinavian dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
59 Scandinavian dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
60 Scandinavian dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
61 Alternate dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
71 Latin-1 case-sensitive, for use with 1252 character set.
72 Latin-1 case-insensitive, for use with 1252 character set.
73 Danish/Norwegian case-sensitive sort order for code page 1252.
74 Finnish/Swedish case-sensitive sort order for code page 1252.
75 Icelandic case-sensitive sort order for code page 1252.
80 Binary order, for use with the 1250 (Central European) character set.
81 Dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
82 Dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
83 Czech dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
84 Czech dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
85 Hungarian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
86 Hungarian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
87 Polish dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
88 Polish dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
89 Romanian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
90 Romanian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
91 Croatian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
92 Croatian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
93 Slovak dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
94 Slovak dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
95 Slovenian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
96 Slovenian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
97 Windows Polish case-sensitive sort order for code page 1250.
98 Windows Polish case-insensitive sort order for code page 1250.
104 Binary order, for use with the 1251 (Cyrillic) character set.
105 Dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set.
106 Dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set.
107 Ukrainian dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set.
108 Ukrainian dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set.
112 Binary order, for use with the 1253 (Greek) character set.
113 Dictionary order, case-sensitive, for use with the 1253 (Greek) character set.
114 Dictionary order, case-insensitive, for use with the 1253 (Greek) character set.
120 Mixed dictionary order, for use with the 1253 (Greek) character set.
121 Dictionary order, case-sensitive, accent-sensitive, for use with the 1253 (Greek) character set.
124 Dictionary order, case-insensitive, accent-insensitive, for use with the 1253 (Greek) character set.
128 Binary order, for use with the 1254 (Turkish) character set.
129 Dictionary order, case-sensitive, for use with the 1254 (Turkish) character set.
130 Dictionary order, case-insensitive, for use with the 1254 (Turkish) character set.
136 Binary order, for use with the 1255 (Hebrew) character set.
137 Dictionary order, case-sensitive, for use with the 1255 (Hebrew) character set.
138 Dictionary order, case-insensitive, for use with the 1255 (Hebrew) character set.
144 Binary order, for use with the 1256 (Arabic) character set.
145 Dictionary order, case-sensitive, for use with the 1256 (Arabic) character set.
146 Dictionary order, case-insensitive, for use with the 1256 (Arabic) character set.
152 Binary order, for use with the 1257 (Baltic) character set.
153 Dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
154 Dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
155 Estonian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
156 Estonian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
157 Latvian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
158 Latvian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
159 Lithuanian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
160 Lithuanian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
183 Danish/Norwegian dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
184 Swedish/Finnish (Standard) dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
185 Swedish/Finnish (Phone) dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
186 Icelandic dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
192 Binary order, for use with the 932 (Japanese) character set.
193 Dictionary order, case-insensitive, for use with the 932 (Japanese) character set
194 Binary order, for use with the 949 (Korean) character set.
195 Dictionary order, case-insensitive, for use with the 949 (Korean) character set.
196 Binary order, for use with the 950 (Traditional Chinese) character set.
197 Dictionary order, case-insensitive, for use with the 950 (Traditional Chinese) character set.
198 Binary order, for use with the 936 (Simplified Chinese) character set.
199 Dictionary order, case-insensitive, for use with the 936 (Simplified Chinese) character set.
200 Dictionary order, case-sensitive, for use with the 932 (Japanese) character set.
201 Dictionary order, case-sensitive, for use with the 949 (Korean) character set.
202 Dictionary order, case-sensitive, for use with the 950 (Traditional Chinese) character set.
203 Dictionary order, case-sensitive, for use with the 936 (Simplified Chinese) character set.
204 Binary order, for use with the 874 (Thai) character set.
205 Dictionary order, case-insensitive, for use with the 874 (Thai) character set.
206 Dictionary order, case-sensitive, for use with the 874 (Thai) character set.

You know that I know that you know that I don't know
Отговор
в отговор на naskovoto #6

Стъпка 2:

За да промениш COllation Type На база данни, трябва да я прекомпилираш. Да не с ерпавя на рпеводач, копирам ти хелп файла, прочети го внимателно, следвай стъпките и си настрой базата в подходяща кодова таблица.

How to rebuild the master database (Rebuild Master utility)
To rebuild the master database

Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.


In the Rebuild Master dialog box, click Browse.


In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.


Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.

In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.

You know that I know that you know that I don't know
Отговор
в отговор на Long Runner #7

Re:

bezkraino sym ti blagodaren :))
ama naistina e malko obezkurajavashto ako napravish vsichko tova i nakraq PHP-to ti kaje:

"Warning: MS SQL message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16) in ..."

tuka veche bqh hvyrlen v zaitnot i reshih da priloja sledniq podhod:

ako iskame da namerim malka ili golqma bukva "a" (primerno) zapisana v varchar s unicode eto kakvo pravim: (simvolite ne sa tochno tezi ama ideqtaq e slednata)

select * from table where field like '%[РС][ѕЉ]%';
kydeto "a" = "Рѕ" a "A" = "СЉ"

syotvetno za celta se pravi kraen avtomat koito da parsira vhodnite simvoli i da generira takiva dvoiki pri nujda :))

hahahaha sha poludeq *&^%#*@&^%$

mamma mia

Редактирано от naskovoto на

Отговор
в отговор на Long Runner #8

IGNORE ME

ignore

Редактирано от naskovoto на

Отговор
в отговор на naskovoto #9

Вече си на прав път:)

Да, за да решиш задачата дето я описа - трябва да стане на ниво Application (а не на DB-ниво), т.е. цялата логика трябва да се обработи от PHP или каквато среда ползваш там - не е възможно да стане на ниво DB-Server (поне аз така мисля)...
M$ SQLServer (става въпрос за SQLServer7.0, защото той ми е пред очите, ама и за SQL2000 съм сигурен че е така) (а и останалите DBMS) поддържа само 1 Default Unicode-таблица и 1 сортиране (Unicode CaseSensitive или UnicodeCaseInsensitive) - това го указваш още при инсталацията, но можеш да го промениш и по-късно (като Reload-неш Master-db - както вече беше споменато)...Така че ако очакваш да имаш Chinese,Hebrew,Arabic,Cyrillic,Latin и да търсиш в такава среда - само на ниво Application трябва да се работи, т.е. за всеки символ му намираш Unicode-кода, малко (!!!) обработка и тогава заявка - искрено ти съчувствам.

Отговор
в отговор на тъп4o #10

Ima i drugo.

SQL Server 7/2000 ima problem pri avtomatichno preformatirane na razlichnite variables ot tranact SQL kym server-side programirane, sirech PHP, ASP, JSP, ASP.NET i t.n. Specialno e ukazano, che izpolvaneto na text, nText i image si e strogo lichno i ne tyrpi avtomatichno formatirane. Preporychva se da ne se izpolva text i nText za Text Searching i Index Searching.

S edna duma, polzvai nvarchar navseide, i ser azrovi za Regular Expressions, zastoto chrez regular ezpressions i wild cards mojesh da ukajeshd a tyrsi vsichko bez znachenie malki golemi bukvi. Ne znam obache s PHP kak e, s C# e meko kazano lesno :-))

You know that I know that you know that I don't know
Отговор
в отговор на Long Runner #11

Re: Ima i drugo.

RegularExpressions е елегантно решение на проблема:)).

Би трябвало да работи и за chinese,hebrew,arabic - стига да знаеш символите в тези езици (а може и да няма главни букви в тях - не знам - хаха).

Отговор
в отговор на тъп4o #12

Imaiki vpredvid,

che za chist utf-8 kodova tablica, sirech English i latinica kato cialo, ta za 127 simvola, izchetoh edna biasna kniga za Regular Expressions i pak nisto ne mi stana iasno, horata po celi traktati i trudove zastitavat, otkachena istoria.

Ta za 127 kodovi simvola knijleto beshe pylno s variacii na tema kak da se rovim i tyrsim, i skromni 240 stranichki.
Mi v Unicode sa 65 000 simvola, stava interesno, ste padne chetene. Ako e za chista kirilka ili latinica, lesno, ako e za simvoplnite ezici kato arabic, japan, i t.n.... ne znam dori niakoi bi li se nael za takova nesto :-)

You know that I know that you know that I don't know
Отговор
в отговор на Long Runner #13

mersi vi mnogo

chestno vi kazvam mnogo sym priqtno iznenadan ot zainteresovanostta vi po problema :)

ako teq hora iskat da si pusnat nemski - ami shte im dobavim umlaudite kakto kirilskite bukvi i shte im vyrvi vsichko :)
za kitaiski da si go pravqt sami ako iskat.

sega se seshtam kakvo li shte stane ako iskat da im vyrvqt ieroglifite otgore nadolu hahahaha.

blagodarsko.

mamma mia
Отговор