SQL query

1

if the code column contains S12 or S10 more then once then multiple otherwise null

select * from (

select 'S1056 S1020' as Code
union
select 'S1200 S1210 W010 T835'
union
select 'S1056'
union
select 'S1200 X1020'
) main

result

Code

S1056
S1056 S1020
S1200 S1210 W010 T835
S1200 X1020

expected result

Code Flag

S1056 NULL
S1056 S1020 Multiple
S1200 S1210 W010 T835 Multiple
S1200 X1020 NULL

2

This might do it:

with main as
(

select code from (

select 'S1056 S1020' as Code
union
select 'S1200 S1210 W010 T835'
union
select 'S1056'
union
select 'S1200 X1020'
) main
)

select code,
       case when _2nd.s10 >0 Or _2nd.s12 > 0
			then 'Multiple'
	   end
from main
cross apply
(
	select PATINDEX('%s10%', Code)
	     , PATINDEX('%s12%', Code)
) _1st(s10, s12)
cross apply
(
	select CASE WHEN _1st.s10 > 0
					THEN PATINDEX('%s10%', substring(code,_1st.s10 + 3, len(code)))
		   END
	     , CASE WHEN _1st.s12 > 0
					THEN PATINDEX('%s12%', substring(code,_1st.s12 + 3, LEN(code)))
		   END
) _2nd(s10, s12)
1 Like
3

Thanks so much :slightly_smiling:

Your too good!!

4

Assuming that S10 and S12 together should not match, what about the code below instead. The extra space is just to make sure that the match is only to the leading part of the value, that is, that is somehow a value such as "0s101" go in there, it would not cause a false match.

select code, 
    case when ' ' + Code like '% s10% s10%' Or 
              ' ' + Code like '% s12% s12%' then 'Muliple'
         else null end as code_flag
from main