0

I found myself performing a rake task to replace with - the first_name and last_name nil of the Client model, but later I was surprised that there was not only nil in both fields, but also '' ' ' ' ' and more, is there a way to identify the customers that meet all these conditions without having to go through all the Clients?

To clarify, previously the Client model did not have validation for these fields.

Juanse Gimenez
  • 481
  • 4
  • 14
  • Try `Client.where.not("first_name > ''")` if this works for you – Deepesh Jun 14 '21 at 12:48
  • it works with some cases, for example the Client that I have with first_name: ' ' is not returned by this query, but it is an advance. thanks! – Juanse Gimenez Jun 14 '21 at 13:06
  • This might help: https://stackoverflow.com/questions/8470813/how-do-i-check-if-a-column-is-empty-or-null-in-mysql – Deepesh Jun 14 '21 at 13:08
  • 1
    `Client.where("NULLIF(TRIM(first_name),'') IS NULL")` should handle all of the above cases assuming that those are actually blank spaces and not unprintable characters like horizontal tab. You can then follow this with an `update_all` call to update all the records to meet your criteria if needed. – engineersmnky Jun 14 '21 at 13:39

1 Answers1

0

Thanks @engineersmnky, your answer helped my solution.

for an update of attribute with the next cases:

  • NULL
  • ''
  • ' '
  • ' ' * n

the solution: Model.where("NULLIF(TRIM(col),'') IS NULL")

Juanse Gimenez
  • 481
  • 4
  • 14