4

I have a MySQL table that I am acessing from R using the package "RODBC". A few of the columns has long concatenated strings separated by the STX character (\x02). I have shown a screen print of the the character below (the character is circled):

STX Symbol

I would like to run the following query from R (RODBC package) to replace the STX delimiter with a different character:

SELECT REPLACE(columnName, '**\x02**', '#') FROM tableName

I have given the complete code below:

library(RODBC)
myconn <-odbcConnect("mydsn")
query <- "SELECT REPLACE(columnName, '\x02', '#') FROM tableName"
queryResults <- sqlQuery(myconn, query)

Is there a way to accomplish this from R?

Ravi
  • 3,223
  • 7
  • 37
  • 49

1 Answers1

1

RODBC includes the function sqlQuery(), which lets you send SQL queries to the connection. According to the documentation, "The term ‘query’ includes any valid SQL statement including table creation, alteration, updates etc as well as SELECTs. The sqlQuery command is a convenience wrapper that first calls odbcQuery and then sqlGetResults. If finer-grained control is needed, for example over the number of rows fetched, additional arguments can be passed to sqlQuery or the underlying functions called directly."

TARehman
  • 6,659
  • 3
  • 33
  • 60
  • TARehman...I understand that...the problem with my case is that I need to replace the symbol (show by the circle in the figure)...I could directly use that symbol in MySQL...However, I am unable to use the symbol in R. I would like to know if it is possible to query for the symbol either using 'x02' or some other means. – Ravi Apr 15 '13 at 17:09
  • I have edited the original post to include the code that I tried. Hope this is clearer. – Ravi Apr 15 '13 at 17:18
  • I'm sorry, I'm confused. What isn't working? I think you _can_ query for the symbol. Perhaps you should use \\x02. – TARehman Apr 15 '13 at 19:03