how to find index of the first capital letter in mysql, cyrillic characters -


i have table geographical names of places. have additional letters in front. example if city name astana s.astana. need name of place astana. want use substr, can't find index of first capital letter.owh ya cyrillic characters used problem. want give examples:

  1. Акмолинская область -> Акмолинская
  2. Кокшетау Г.А. -> Кокшетау
  3. г.Кокшетау -> Кокшетау
  4. Красноярский с.о. -> Красноярский
  5. с.Красный Яр -> Красный Яр

what have tried: created field short_nameru , updated way 

update center_kato ck set ck.short_nameru = case when    length(substring_index(ck.nameru , ' ', -1)) > length(substring_index(ck.nameru , ' ',1))   substring_index(ck.nameru , ' ', -1)   else substring_index(ck.nameru , ' ',1)    end

and after updated again 

update center_kato ck set ck.short_nameru = case    when length(substring_index(ck.short_nameru , '.', -1)) > length(substring_index(ck.short_nameru , '.',1))   substring_index(ck.short_nameru , '.', -1)   when length(substring_index(ck.short_nameru , '.', -1)) < length(substring_index(ck.short_nameru , '.',1))    substring_index(ck.short_nameru , '.',1)   else ck.short_nameru    end

but lost needed values. "зимовка Туяк" became "зимовка" needed "Туяк"

i cannot think of way find pattern in string. here brute force approach:

select (case when ascii(substr(cityname, 1, 1)) between ascii('a') , ascii('z')              cityname              when ascii(substr(cityname, 2, 1)) between ascii('a') , ascii('z')              substr(cityname, 2)              when ascii(substr(cityname, 3, 1)) between ascii('a' , ascii('z')              substr(cityname, 3)              when ascii(substr(cityname, 4, 1)) between ascii('a') , ascii('z')              substr(cityname, 4)              when ascii(substr(cityname, 5, 1)) between ascii('a') , ascii('z')              substr(cityname, 5)              when ascii(substr(cityname, 6, 1)) between ascii('a') , ascii('z')              substr(cityname, 6)              when ascii(substr(cityname, 7, 1)) between ascii('a') , ascii('z')              substr(cityname, 7)              when ascii(substr(cityname, 8, 1)) between ascii('a') , ascii('z')              substr(cityname, 8)              when ascii(substr(cityname, 9, 1)) between ascii('a') , ascii('z')              substr(cityname, 9)              when ascii(substr(cityname, 10, 1)) between ascii('a') , ascii('z')              substr(cityname, 10)              else cityname         end)

if know preceding character '.', there other approaches.

edit:

the following seems working in cyrillic:

select         (case when substr(nameru, 1, 1) between binary 'А' , binary 'Я' nameru     when substr(nameru, 2, 1) between binary 'А' , binary 'Я' substr(nameru, 2)     when substr(nameru, 3, 1) between binary 'А' , binary 'Я' substr(nameru, 3)     when substr(nameru, 4, 1) between binary 'А' , binary 'Я' substr(nameru, 4)     when substr(nameru, 5, 1) between binary 'А' , binary 'Я' substr(nameru, 5)     when substr(nameru, 6, 1) between binary 'А' , binary 'Я' substr(nameru, 6)     when substr(nameru, 7, 1) between binary 'А' , binary 'Я' substr(nameru, 7)     when substr(nameru, 8, 1) between binary 'А' , binary 'Я' substr(nameru, 8)     when substr(nameru, 9, 1) between binary 'А' , binary 'Я' substr(nameru, 9)     when substr(nameru, 10, 1) between binary 'А' , binary 'Я' substr(nameru, 10)          else nameru         end) mytable;

(for reason, editors rejected edit yernanun, i'm including it.)


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more