t-sql How to use name column in patindex

Question

could you help me with such question: I have such request

with a as  
(
    select 1 num, 21 q, 49 b,  100 scq, 155 o, '49 55;'  b_ost
    union
    select 2 num, 21 q, 50 b,  101 scq, 155 o,'50 54;49 55;'
    union
    select 3 num, 21 q, 48 b,  156 scq, 254 o, '48 98;50 54;49 55;'
    union
    select 4 num, 21 q, 49 b,  156 scq, 254 o,  '49 98;48 98;50 54;49 55;'
    union
    select 5 num, 21 q, 48 b,  150 scq, 254 o,   '48 104;49 98;48 98;50 54;49 55;'
)
select *  ,  PATINDEX('%48 %', b_ost), PATINDEX(b , b_ost)   from a

I need to find first position column b in column b_ost If i use PATINDEX('%48 %', b_ost) then position calculate right. But i need to use column b instead of '%48 %' and if i will use PATINDEX(b , b_ost) then position b into column b_ost will 0. Solution through a variable is not suitable. Please tell me how can I solve my issue?

Answer 1

Assuming I've understood your requirements correctly, the code below will do it:

select * ,
       PATINDEX('%'+ CAST(b AS varchar)+'%', b_ost) from a

It always returns "1" with the sample data you kindly provided but I made a couple of changes to your sample and it works fine.