Substring the following strings

Question

How can I add a code that substrings the below strings?
I need the questionID from the variableID, that is, anything between the two underscores (_). Examples:

• hcm_q10_568
• py_q10x_45
• sub_q1_34
• la_q8a_67

I have this code that works for specific style string but doesn't satisfy all:

(substring(@s,charindex('q'',@s),charindex('_',reverse(@s))))

Could you give me some ideas? How to get this working for all the strings.

Answer 1

using parsename() (if there are no periods in the strings):

select 
    middle = parsename(replace(str,'_','.'),2)
from t

rextester demo: http://rextester.com/LWNP30293

returns:

+--------+
| middle |
+--------+
| q10    |
| q10x   |
| q1     |
| q8a    |
+--------+

---

For strings with periods, you can use a temporary replacement character and swap it out and back in again:

select 
    middle = replace(parsename(replace(replace(str,'.',char(29)),'_','.'),2),char(29),'.')
from t

Reference:

• Using the PARSENAME function to split delimited data - Damian Maclennen - 2003-11-10

---

If you really want to use substring() and charindex() you can like so:

select 
     middle=substring(str
      ,(charindex('_',str)+1)
      ,(charindex('_',str,(charindex('_',str)+1))-charindex('_',str)-1)
    )
from t

Answer 2

Use CHARINDEX. Terse, but foolproof.

declare @input varchar(50)
declare @index1 int, @index2 int
declare @result varchar(50)
select @input = 'my_string_here';
select @index1=CHARINDEX('_', @input, 1)
if (@index1 > 0)
begin
   select @index2 = CHARINDEX('_', @input, @index1+1)
   if (@index2 > 0)
        select @result = SUBSTRING(@input, @index1+1, @index2-@index1-1)
end
print @result