Setting variable to substring of itself for query

Question

I have a query I am using to return values:

DECLARE @ VALUE varchar(1000) = 'ACELLA PHARMA [A200]' 
select
   a.item_id,
   attr_vals = concat(a.attr_val, ' ', quotename(b.attr_val)) 
from
   ndc_attr as a 
   left outer join
      [NDC_ATTR] b 
      on b.field_id = 144 
      and a.field_id = 225 
where
   b.attr_val is not null 
   and b.attr_val like '%' +@ VALUE + '%'

The thing is I need the value within the parenthesis: A200 in this example.

I have tried to break up the query by adding an if statement in it:

if @VALUE like '%[%' and @VALUE like '%]%'
begin
SET @VALUE = SUBSTRING(@VALUE, CHARINDEX('[', @VALUE) + 1, LEN(@VALUE) - 1)
end

But oddly, the full string is returning ('ACELLA PHARMA [A200]').

How can I break up this value so only the string within the brackets is on @VALUE?

this may help http://stackoverflow.com/questions/4021507/mysql-use-regex-to-extract-string-select-regex — Joe T, Mar 09 '17 at 20:18
I'll restore it, but Gordon's is better in my opinion... you should use his. — Dave C, Mar 09 '17 at 20:53

Gordon Linoff · Accepted Answer · 2017-03-10T02:32:30.243

2

If you want the part in the square braces and the values look the the example, then here is a shortcut:

select replace(stuff(@value, 1, charindex('[', @value), ''), ']','')

I am not sure what the query has to do with the question, but the sample logic can be applied in a query, of course.

edited Mar 10 '17 at 02:32

answered Mar 09 '17 at 20:14

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Mar 09 '17 at 20:46

You are close... this should do it.

declare @value varchar(max)
set @value = 'ACELLA PHARMA [A200]' 

if charindex('[',@value)>0 and charindex(']',@value)>0
begin
    select @value=substring(@value, charindex('[', @value)+1, len(@value) - charindex('[', @value)-1)
end
select @value

score 1 · Answer 3 · answered Mar 09 '17 at 20:53

Are you trying to do something like this?

declare @value varchar(1000) = 'ACELLA PHARMA [A200]' 
declare @b_attr_val varchar(16);
/* Gordon Linoff's answer here: */
set @b_attr_val= replace(stuff(@value, 1, charindex('[', @value), ''), ']','');

select
    a.item_id
  , attr_vals = concat(a.attr_val, ' ', quotename(b.attr_val)) 
from ndc_attr as a 
   left outer join [NDC_ATTR] b 
      on b.field_id = 144 
     and a.field_id = 225 
where b.attr_val = @b_attr_val;

Setting variable to substring of itself for query

3 Answers3