# Select entity whose elementCollection field contains some like string

**URL:** <https://discourse.hibernate.org/t/select-entity-whose-elementcollection-field-contains-some-like-string/7406>\
**Category:** Hibernate ORM\
**Created:** [March 19, 2023, 2:03pm UTC](https://discourse.hibernate.org/t/select-entity-whose-elementcollection-field-contains-some-like-string/7406 "2023-03-19T14:03:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Archsx](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/archsx/32/2329_2.png) [@Archsx](https://discourse.hibernate.org/u/Archsx)\
**Post date:** [March 19, 2023, 2:03pm UTC](https://discourse.hibernate.org/t/select-entity-whose-elementcollection-field-contains-some-like-string/7406/1 "2023-03-19T14:03:44Z")

</div>

so here is [link](https://stackoverflow.com/questions/75782457/hibernate-select-entity-whose-elmentcollection-contains-some-like-string) of the question on stackoverflow.

anyway, the question is

say we have a entity named Profile

```auto
@Entity
@Serializable
@Table(name = "profile")
class Profile {

    @Id @GeneratedValue var id: Long = 0

    @ElementCollection
    @CollectionTable(name = "profile_attachment")
    @Column(length = 1024, nullable = false)
    @LazyCollection(LazyCollectionOption.FALSE)
    lateinit var attachments: Set<String> // could be something like ["/somepath/to/foo.png","/somepath/to/bar.png"]

    constructor()

}

```

I wanna select the entity whose attachments contains some string which include some character, for example, select entity whose attachments that has one item contains ‘foo’ . I’ve tried the following hql queries, but **all failed** :

```auto
profileRepository.list(" some elements(attachments) like '%foo%'")

profileRepository.list(" '%foo%' in elements(attachments) ")

profileRepository.list(" '%foo%' = some elements(attachments) ")

```

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 20, 2023, 8:20am UTC](https://discourse.hibernate.org/t/select-entity-whose-elementcollection-field-contains-some-like-string/7406/2 "2023-03-20T08:20:22Z")

</div>

It’s pretty easy actually if you know the basics of SQL. This kind of query is usually formulated with an `EXISTS` subquery:

`exists (select 1 from p.attachments a where a like '%foo%')`