Remove part of multiline string after pattern in r

Question

I am trying to remove all the characters from ROW FORMAT SERDE, with the gsub function however it does not work. Any suggestion.

x <- c("CREATE TABLE `cld_ml_bi_eng.iris`(", "  `sepal_length` double, ", 
  "  `sepal_width` double, ", "  `petal_length` double, ", "  `petal_width` double, ", 
  "  `species` string)", "ROW FORMAT SERDE ", "  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' ", 
  "STORED AS INPUTFORMAT ", "  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' ", 
  "OUTPUTFORMAT ", "  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'", 
  "LOCATION", "  'hdfs://haprod/warehouse/tablespace/managed/hive/cld_ml_bi_eng.db/iris'", 
  "TBLPROPERTIES (", "  'bucketing_version'='2', ", "  'transactional'='true', ", 
  "  'transactional_properties'='default', ", "  'transient_lastDdlTime'='1636686825')")

Here I use gsub

gsub(pattern = "(ROW FORMAT SERDE).*", replacement = "\\1", x = x)

My expected output

c("CREATE TABLE `cld_ml_bi_eng.iris`(", "  `sepal_length` double, ", 
  "  `sepal_width` double, ", "  `petal_length` double, ", "  `petal_width` double, ", 
  "  `species` string)")

Answer 1

One approach would be to use grep to find the index of the string in your input vector which starts with the text ROW FORMAT SERDE. Then, subset the input vector and paste into a single string:

paste0(x[1:(grep("^ROW FORMAT SERDE", x)-1)], collapse="")

[1] "CREATE TABLE cld_ml_bi_eng.iris( sepal_length double, sepal_width double, petal_length double, petal_width double, species string)"