Retrieve text between two periods in a value

I’ve been spinning around a bit on how to accomplish this in SQL DW. I need to extract the text between two periods in a returned value. So my value returned for Result is:

I’m trying to extract the values between period 1 and 2, so the red portion above:

The values will be a wide variety of lengths.

I’ve got this code: substring(Result,charindex('.',Result)+1,3) as ResultMid that results in this:

My problem is I’m not sure how to get to a variable length to return so that I can pull the full value between the two periods. Would someone happen to know how I can accomplish this?

Thx, Joe

Answer

We can build on your current attempt:

substring(
    result,
    charindex('.', result) + 1,
    charindex('.', result, charindex('.', result) + 1) - charindex('.', result) - 1
)

Rationale: you alreay have the first two arguments to substring() right. The third argument defines the number of characters to capture. For this, we compute the position of the next dot (.) with expression: charindex('.', result, charindex('.', result) + 1). Then we substract the position of the first dot from that value, which gives us the number of characters that we should capture.

Demo on DB Fiddle:

result                   | result_mid
:----------------------- | :---------
sam.pdc.sys.paas.l.com   | pdc       
sm.ridl.sys.paas.m.com   | ridl      
s.sandbox.sys.paas.g.com | sandbox