0

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?

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
kir kir
  • 3
  • 2

1 Answers1

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.

Squirrel
  • 23,507
  • 4
  • 34
  • 32
BWFC
  • 146
  • 3
  • You dropped a space character after the numeric value. `+'%'` ought to be `+'% '`. – HABO Jul 27 '22 at 12:50
  • @HABO, are you sure about that? It returns 0 every time as `+'% '`. `+' %'` does give valid results though. – BWFC Jul 27 '22 at 13:28
  • My bad. The OP's original pattern was `'%48 %'`, so it was only matching the first in each pair of semicolon (`;`) delimited values, so the pattern should have `+' %'`. Their sample data doesn't have any interesting rows, i.e. ones where the match doesn't occur at the beginning of the string or where `b` is `42` and the string starts with `'42999 ...'`. – HABO Jul 27 '22 at 13:36