Question: I am not sure whats the problem with mysql statement. I am trying to run the below query which I took from Repository Class in eclipse from my project.

```
select distinct(al.entity) from Allocation al where al.status = 1 and  al.userDepartment.user is not null 
and al.userDepartment.department.costCode in ('finance')
and al.entity.locationFloor.id = 1 ;

```

and it shows me error on `.costCode` as well as on `.id` saying `'.' is not valid at this position, Expecting : EOF, ';'`

Any help would be appreciated. Thanks in advance

Allocation table

```
Id user_department_id entity_id status
1        1              1        1

```

Entity table

```
id, entity_type_id, entity_child_type_id, location_floor_id, status
1        1                1                     1               1

```

User\_Department table

```
id, user_id department_id status
1      3         4           1

```

Department table

```
id name    cost_code  status
1  finance     011      1

```

MYSQL-Workbench version is 8.0

Answer: 
You can use :

```
transform(df, WORDS_A_R  = ifelse(grepl("^[AR]", WORD_LIST), WORD_LIST, NA))

```

If you prefer `dplyr` :

```
library(dplyr)
df %>%
  mutate(WORDS_A_R  = ifelse(grepl("^[AR]", WORD_LIST), WORD_LIST, NA))

#  ID WORD_LIST WORDS_A_R
#1  1     APPLE     APPLE
#2  2       RED       RED
#3  3      SNOW      <NA>
#4  4    ANKARA    ANKARA
#5  5     RENEW     RENEW

```
You can also use `base R` with `substring()` function:

```
#Create condition
cond1 <- substr(DF$WORD_LIST,1,1)=='A' | substr(DF$WORD_LIST,1,1)=='R'
#Create var
DF$WORDS_A_R <- NA
DF$WORDS_A_R[cond1]<-DF$WORD_LIST[cond1]

ID WORD_LIST WORDS_A_R
1  1     APPLE     APPLE
2  2       RED       RED
3  3      SNOW      <NA>
4  4    ANKARA    ANKARA
5  5     RENEW     RENEW

```