Thank you for your reply!
Interestingly enough, if I run the second option (PATINDEX) I see a 3sec improvement, which is huge, because the initial run was 4-5sec and this...

Unfortunately, both wildcards are required. I will update my initial post with this info, as well.

I did try the other way and it's the number 4 point in the initial description of the issue. It doesn't work unfortunately.

Yes, but that would be the situation where you want to insert data into a memory optimized table from a disk-based table from another db. My need was to read...

I just tried with a view as well, but it doesn't work. I guess with the function it selects everything from the table, encapsulates the whole data and then it...

It works! It's slow, but it works.

Thank you so much for your reply!

Thank you Luis, for taking the time and explaining it. I understood now, and agree that given a large set of data, my solution would have painful performance, in comparison...

Assuming you have an identity on the input table, to identify the row (row_id) you could try something like this:

1. Identify the object_id:

select *

Thank you Thom and Steve!

Just realized I opened the question in my browser and left it open for a while and posted a reply without refreshing the content...

An object can be locked at different levels, using hints, for example: 
select column
from

WITH (ROWLOCK)
-> Row Lock

or 
select column
from

...

Hello!

If I understood correctly, in the last image above (with the results), in the column [START_EMP_STATUS_KEY], the position of the 1 is wrong. It shouldn't be at "At...

Thank you for your reply, I was thinking of doing something like this, but it's ugly (considering the business requirements aswell), so I thought maybe there would be another option. 

Thank you so much for everyone's interest.

In the end, I found a few more possible patterns and I used the following regex to manage to identify all of...

It's a few thousand. But the query can run for a few minutes if needed. It doesn't need to work really fast.