In the first table, where the data is t_info_u129279_cpn_1 , the name of the column is dump , and in the second, where the numbers are simply, the name of the column is user_id that is, the unique user ID after _u (t_info_u129279_cpn_1) , the company number after _cpn_ (t_info_u129279_cpn_1)

I need to compare with the second table the company numbers and user numbers. The problem is that the data in the first table is different: some rows contain t_info_u129279_cpn_1 , some t_info_cpn_1_u129279 delphi

Tell me how to write the correct code in SQL? Need to somehow isolate the numbers from the database?

  • And the database is still MySQL or postgress. And then there’s a need, and he considers underscore for any character, it’s necessary to replace. And the replacement functions in different databases are different ... - Mike

1 answer 1

For MySQL, something like this (there is nothing to check for, you did not provide a ready base).

 select * from Table1 A join Table2 B on replace(concat(A.dump,'-'),'_','-') like concat('%-u',B.user_id,'-%')

You have to change the underscore on a dash, because as in it means 1 any character. In Postgress, the same thing should work, the replacement function is also called. I hope for cpn do it yourself by analogy.

UPD : According to the recommendations of @msi, the last line can be simplified to:

  concat(A.dump,'-') like concat('%|_u',B.user_id,'|_%') ESCAPE '|'
  • You do not need to replace it if you use the escape character. - msi
  • @msi, by the way, what, I did not find the way how it is set - Mike
  • @Mike, for example, A.dump like concat('%|_u',B.user_id,'|_%') ESCAPE '|' - msi
  • Thank you very much! everything works - Carol