在SQL中提取两个字符之间的文本

I am trying to extract the text between two characters using t-sql. I have been able to write it where it pulls the information close to what I want, but for some reason I am not getting what i am expecting(suprise, suprise). Could really use alittle help refining it. I am trying to extract part of the table name that is located between two [ ]. An example of the column data is as follows(this is a table that records all changes made to the database so the column text is basically SQL statements):

我试图使用t-sql提取两个字符之间的文本。我已经能够把它写在接近我想要的信息的地方,但由于某种原因我得不到我所期待的(惊讶,惊讶)。真的可以用它来帮助改进它。我试图提取位于两个[]之间的部分表名。列数据的示例如下(这是一个记录对数据库所做的所有更改的表,因此列文本基本上是SQL语句):

ALTER TABLE [TABLENAME].[MYTABLE] ADD
[VIP_CUSTOMER] [int] NULL

I am trying to extract part of the table name, in this example I just want 'MYTABLE'

我试图提取部分表名,在这个例子中我只想要'MYTABLE'

Right now I am using:

现在我正在使用:

select  SUBSTRING(db.Event_Text, CHARINDEX('.', db.Event_Text) + 2, (CHARINDEX(']', db.Event_Text)) - CHARINDEX('', db.Event_Text) + Len(']')) as OBJName
FROM DBA_AUDIT_EVENT DB
WHERE DATABASE_NAME = 'XYZ'

But when I use this, I don't always get the results needed. Sometimes I get 'MYTABLE] ADD' and sometimes I get the part of the name I want, and sometimes depending on the length of the tablename I only get part the first part of the name with part of the name cut off at the end. Is there anyway to get this right, or is there a better way of writing it? Any help would be greatly appreciated. Thanks in advance.

但是当我使用它时,我并不总是得到所需的结果。有时我得到'MYTABLE] ADD'有时候我得到了我想要的名字的一部分,有时候根据表名的长度,我只得到名字的第一部分,最后部分名称被截断。无论如何要做到这一点,还是有更好的写作方式?任何帮助将不胜感激。提前致谢。

3 个解决方案

#1

Long, but here's a formula using the brackets:

很长,但这是使用括号的公式:

Declare @text varchar(200);
Select @text='ALTER TABLE [TABLENAME].[MYTABLE] ADD [VIP_CUSTOMER] [int] NULL';
Select SUBSTRING(@text, 
                 CHARINDEX('[', @text, CHARINDEX('[', @text) + 1 ) +1, 
                 CHARINDEX(']', @text, CHARINDEX('[', @text, CHARINDEX('[', @text) + 1 ) ) -
                    CHARINDEX('[', @text, CHARINDEX('[', @text) + 1 ) - 1 );

Replace @text with your column name.

用您的列名替换@text。

#2

Give this a shot:

给这个镜头:

 select SUBSTRING(db.Event_Text, CHARINDEX('.', db.Event_Text) + 2
, CHARINDEX(']', db.Event_Text) - 2) as OBJName
FROM DBA_AUDIT_EVENT DB
WHERE DATABASE_NAME = 'XYZ'

#3

this is a pretty ugly way to get the length, but I've used something like this before:

这是一个非常丑陋的方式来获得长度,但我之前使用过类似的东西:

select  SUBSTRING(db.Event_Text, 
    CHARINDEX('.', db.Event_Text) + 2,  
    charindex('] ADD',db.Event_Text) - CHARINDEX('.',db.Event_Text)-2))

Give it a try, it may work for you.

试一试,它可能适合你。

#1