0

I have a DB with Chinese collation (type: Chinese_PRC_CI_AS). When i read this DB into Rstudio, all the Characters becomes ????. I tried changing System Locale to Chinese settings (Sys.setlocale(category="LC_ALL",locale="chinese") but still doesn't work. Any ideas?

SQL DB: Columns in SQL

Columns in RStudio

I've searched the internet to no avail.

Thanks in advance

To provide more information, i'm using mssql, and sessionInfo() is:

R version 3.2.5 (2016-04-14) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 7 x64 (build 7601) Service Pack 1

locale: 1 LC_COLLATE=Chinese (Simplified)_People's Republic of China.936 LC_CTYPE=Chinese (Simplified)_People's Republic of China.936
[3] LC_MONETARY=Chinese (Simplified)_People's Republic of China.936 LC_NUMERIC=C
[5] LC_TIME=Chinese (Simplified)_People's Republic of China.936

attached base packages: 1 stats graphics grDevices utils datasets methods base

other attached packages: 1 RMySQL_0.10.9 DBI_0.5-1 date_1.2-36 data.table_1.10.4 stringr_1.1.0 ggplot2_2.2.1 RODBC_1.3-14 zoo_1.7-14 chron_2.3-49
[10] plyr_1.8.4

loaded via a namespace (and not attached): 1 Rcpp_0.12.9 lattice_0.20-34 assertthat_0.1 grid_3.2.5 gtable_0.2.0 magrittr_1.5 scales_0.4.1 stringi_1.1.2 lazyeval_0.2.0
[10] tools_3.2.5 munsell_0.4.3 colorspace_1.3-2 tibble_1.2

Mike Chen
  • 1
  • 2
  • please specify database ? mysql or mssql or Oracle – srp Mar 31 '18 at 18:41
  • see if you can set the default font in rstudio. Set "Chinese" font as default and see if it is showing correctly – srp Mar 31 '18 at 18:57
  • 1
    Try changing encoding of the DB while importing it to R studio – Ayush Nigam Mar 31 '18 at 19:45
  • Thanks Ayush. I'm not sure that is the approach I think somehow the encoding that is used to read in the SQL table should be specified but I can't find one that works – Mike Chen Apr 01 '18 at 01:46
  • For MySQL, see "question marks" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James May 23 '18 at 18:13

1 Answers1

0

you can do the following:

 select * from table where [conditions]
 [remaining query]
 COLLATE DATABASE_DEFAULT;

use this last line to get the req collation.

ahsen_mukhtar_maken
  • 21
  • 2
  • Thanks Ahsen. I tried this but still not working. I also tried to specify DBMSsencoding to UTF-8 and still do not work – Mike Chen Apr 01 '18 at 01:36