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?

I'll restore it, but Gordon's is better in my opinion... you should use his. — Dave C
– Dave C, Commented Mar 9, 2017 at 20:53

Gordon Linoff · Accepted Answer · 2017-03-10 02:32:30Z

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, 2017 at 2:32

answered Mar 9, 2017 at 20:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave C · Accepted Answer · 2017-03-09 20:46:16Z

1

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

answered Mar 9, 2017 at 20:46

Dave C

7,4821 gold badge21 silver badges30 bronze badges

Comments

SqlZim · Accepted Answer · 2017-03-09 20:53:16Z

1

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;

answered Mar 9, 2017 at 20:53

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Collectives™ on Stack Overflow

Setting variable to substring of itself for query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related