将变量设置为自身的子串以进行查询(Setting variable to substring of itself for query)

将变量设置为自身的子串以进行查询(Setting variable to substring of itself for query)

我有一个查询，我用来返回值：

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 + '%'

问题是我需要括号内的值：在这个例子中是A200。

我试图通过在其中添加if语句来分解查询：

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

但奇怪的是，完整的字符串正在返回（'ACELLA PHARMA [A200]'）。

我怎样才能分解这个值，所以只有括号内的字符串在@VALUE上？

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?

最满意答案

如果您希望方括号中的部分和值看起来是示例，那么这是一个快捷方式：

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

我不确定查询与问题有什么关系，但当然可以在查询中应用示例逻辑。

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.