blocks|key|454869|text|像那样吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|454870|DECLARE+@n+TABLE+(test+VARCHAR(100))

INSERT+@n+(test)
VALUES++('asp:test;abca'),+('asps:tesst;abcsa'),+('asssp:tsest;assbca')

INSERT+@n+(test)
VALUES++('asptest;abca'),+('asps:tesstabcsa'),+('asssptsestassbca')


SELECT+SUBSTRING(test,+CHARINDEX(':',+test)+%2B+1,+ISNULL(NULLIF(CHARINDEX(';',+test),+0),+LEN(test)+%2B+1)+-+CHARINDEX(':',+test)+-+1)
FROM+@n|code-block|syntax|javascript|454871|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Something like that?

<pre><code>DECLARE @n TABLE (test VARCHAR(100))

INSERT @n (test)
VALUES ('asp:test;abca'), ('asps:tesst;abcsa'), ('asssp:tsest;assbca')

INSERT @n (test)
VALUES ('asptest;abca'), ('asps:tesstabcsa'), ('asssptsestassbca')


SELECT SUBSTRING(test, CHARINDEX(':', test) + 1, ISNULL(NULLIF(CHARINDEX(';', test), 0), LEN(test) + 1) - CHARINDEX(':', test) - 1)
FROM @n
</code></pre>

blocks|key|447969|text|to确实有PATINDEX来进行模式匹配，但不支持健壮的regex。但是，如果您拥有Server+2005及更高版本，则可以创建一个CLR用户定义函数。但是，您可以使用PATINDEX/CHARINDEX和SUBSTRING使用常规用户定义的函数来完成所需的任务。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|447970|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/magazine/cc163473.aspx^0|1U|9|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

T-SQL does have PATINDEX to do pattern matching, but does not support a robust regex API. However, if you have SQL Server 2005 and later, you could create a <a href="http://msdn.microsoft.com/en-us/magazine/cc163473.aspx" rel="nofollow">CLR user-defined function</a>. However, you may be able to accomplish what you want with a regular user-defined function using PATINDEX/CHARINDEX and SUBSTRING.

blocks|key|447972|text|对于sql引擎支持的字符串函数:子字符串，charindex：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447973|SUBSTRING(s,+CHARINDEX(':',+s)+%2B+1,+CHARINDEX(';',+s)+-+CHARINDEX(':',+s)+-+1)|code-block|syntax|javascript|447974|带错误处理的完整代码|447975|case+
++when+CHARINDEX(':',+s)+>+0+and+CHARINDEX(';',+s)+>+0+
++++then+SUBSTRING(s,+CHARINDEX(':',+s)+%2B+1,+CHARINDEX(';',+s)+-+CHARINDEX(':',+s)+-+1)+
+else+null
end|447976|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For sql engine supported string functions: substring, charindex:

<pre><code>SUBSTRING(s, CHARINDEX(':', s) + 1, CHARINDEX(';', s) - CHARINDEX(':', s) - 1)
</code></pre>

Full code with error processing

<pre><code>case 
 when CHARINDEX(':', s) &gt; 0 and CHARINDEX(';', s) &gt; 0 
 then SUBSTRING(s, CHARINDEX(':', s) + 1, CHARINDEX(';', s) - CHARINDEX(':', s) - 1) 
 else null
end
</code></pre>

blocks|key|447982|text|下面是一个使用CharIndex的示例，它可以做您想做的事情，如果冒号或分号丢失了，则不会失败。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447983|Declare+@Temp+Table(Test+VarChar(50))

Insert+Into+@Temp+Values('asp:test;abca')
Insert+Into+@Temp+Values('asps:tesst;abcsa')
Insert+Into+@Temp+Values('asssp:tsest;assbca')
Insert+Into+@Temp+Values('Hello+World')
Insert+Into+@Temp+Values('Hello+World:')
Insert+Into+@Temp+Values('Hello+World;')

Select++SubString(Test,+CharIndex(':',+Test)+%2B+1,+CharIndex(';',+Test+%2B+':;')+-+CharIndex(':',+Test)+-+1)
From++++@Temp|code-block|syntax|javascript|447984|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is an sample using CharIndex that does what you want and does not fail if the colon or semi-colon are missing.

<pre><code>Declare @Temp Table(Test VarChar(50))

Insert Into @Temp Values('asp:test;abca')
Insert Into @Temp Values('asps:tesst;abcsa')
Insert Into @Temp Values('asssp:tsest;assbca')
Insert Into @Temp Values('Hello World')
Insert Into @Temp Values('Hello World:')
Insert Into @Temp Values('Hello World;')

Select SubString(Test, CharIndex(':', Test) + 1, CharIndex(';', Test + ':;') - CharIndex(':', Test) - 1)
From @Temp
</code></pre>

blocks|key|455916|text|您可以编写这样的函数来处理所有情况--有冒号(:)和/或分号(;)+--或者根本没有分号。在任何情况下，这都会返回您要查找的字符串片段：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|455917|ALTER+FUNCTION+dbo.ExtractString+(@InputString+VARCHAR(500))
RETURNS+VARCHAR(500)
AS+BEGIN
++++DECLARE+@Result+VARCHAR(500)

++++DECLARE+@ColonPos+INT,+@SemicolonPos+INT

++++SET+@ColonPos+=+CHARINDEX(':',+@InputString)
++++SET+@SemicolonPos+=+CHARINDEX(';',+@InputString)

++++IF+@ColonPos+>+0+
++++++++IF+@SemicolonPos+>+0
++++++++++++SET+@Result+=+SUBSTRING(@InputString,+@ColonPos+%2B+1,+@SemicolonPos+-+1+-+@ColonPos)
++++++++ELSE++++
++++++++++++SET+@Result+=+SUBSTRING(@InputString,+@ColonPos+%2B+1,+500)
++++ELSE
++++++++IF+@SemicolonPos+>+0
++++++++++++SET+@Result+=+SUBSTRING(@InputString,+1,+@SemicolonPos+-+1)
++++++++ELSE++++
++++++++++++SET+@Result+=+@InputString

++++RETURN+@Result
END|code-block|syntax|javascript|455918|因为它只是一个字符串操作函数，所以它在性能上也不应该太差。|455919|如果您对各种输入运行它：|455920|SELECT+
++++dbo.ExtractString('asp:test;abca'),
++++dbo.ExtractString('asps:tesst;abcsa'),+
++++dbo.ExtractString('asssp:tsest;assbca'),
++++dbo.ExtractString('test'),
++++dbo.ExtractString('a:test'),
++++dbo.ExtractString('atest;something')|455921|你会得到以下结果：|455922|test+++tesst+++tsest+++test+++test+++atest|455923|entityMap^0|N|1|V|1|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|15|8|@]|D|@]|E|$]]|$1|S|3|T|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|U|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|V|$]]

You could write a function like this that would handle all cases - there is a colon (<code>:</code>) and/or there is a semicolon (<code>;</code>) - or none at all. In any case, this would return the string fragment you're looking for:

<pre><code>ALTER FUNCTION dbo.ExtractString (@InputString VARCHAR(500))
RETURNS VARCHAR(500)
AS BEGIN
 DECLARE @Result VARCHAR(500)

 DECLARE @ColonPos INT, @SemicolonPos INT

 SET @ColonPos = CHARINDEX(':', @InputString)
 SET @SemicolonPos = CHARINDEX(';', @InputString)

 IF @ColonPos &gt; 0 
 IF @SemicolonPos &gt; 0
 SET @Result = SUBSTRING(@InputString, @ColonPos + 1, @SemicolonPos - 1 - @ColonPos)
 ELSE 
 SET @Result = SUBSTRING(@InputString, @ColonPos + 1, 500)
 ELSE
 IF @SemicolonPos &gt; 0
 SET @Result = SUBSTRING(@InputString, 1, @SemicolonPos - 1)
 ELSE 
 SET @Result = @InputString

 RETURN @Result
END
</code></pre>

Since it's just a string manipulation function, it shouldn't be too bad in terms of performance, either.

If you run it against various inputs:

<pre><code>SELECT 
 dbo.ExtractString('asp:test;abca'),
 dbo.ExtractString('asps:tesst;abcsa'), 
 dbo.ExtractString('asssp:tsest;assbca'),
 dbo.ExtractString('test'),
 dbo.ExtractString('a:test'),
 dbo.ExtractString('atest;something')
</code></pre>

you get these results:

<pre><code>test tesst tsest test test atest
</code></pre>

blocks|key|455925|text|declare+@T+table
(
++Col+varchar(20)
)

insert+into+@T+values
('asp:test;abca'),+
('asps:tesst;abcsa'),+
('asssp:tsest;assbca'),
('asssp:tsestassbca'),
('asssptsest;assbca'),
('asssptsestassbca'),
(':;')

select++left(T2.Col,+charindex(';',+T2.Col%2B';')-1)+
from+@T+as+T1
++cross+apply+(select+stuff(T1.Col,+1,+charindex(':',+T1.Col),+''))+as+T2(Col)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|455926|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>declare @T table
(
 Col varchar(20)
)

insert into @T values
('asp:test;abca'), 
('asps:tesst;abcsa'), 
('asssp:tsest;assbca'),
('asssp:tsestassbca'),
('asssptsest;assbca'),
('asssptsestassbca'),
(':;')

select left(T2.Col, charindex(';', T2.Col+';')-1) 
from @T as T1
 cross apply (select stuff(T1.Col, 1, charindex(':', T1.Col), '')) as T2(Col)
</code></pre>

blocks|key|455945|text|您可以按照其他人的建议使用子字符串或patindex+(我个人的首选是UDF)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|455946|但是，您需要注意，使用这些技术作为WHERE子句的一部分可能会导致表扫描。|455947|根据数据大小、服务器负载和进行此查询的频率，尝试其他方法可能更有意义。例如，使用填充的数据添加另一列。|455948|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

You can use a substring or patindex as suggested by others (My personal preference would be for a UDF). 

However, you need to be aware that using these techniques as part of the WHERE clause will likely cause a table scan. 

Depending on the size of your data, server load, and frequency of making this query, it may make more sense to try something else. For instance, adding another column with this data populated.

I have a column say <code>Test</code>.

The test have values like <code>asp:test;abca</code>, <code>asps:tesst;abcsa</code>, <code>asssp:tsest;assbca</code>

I am only interested in the text before a <code>;</code> and after <code>:</code>, so my result should be like 

<pre><code>test, tesst, tsest
</code></pre>

I just want to do it with a SQL query and not programatically in code.

Does SQL Server support some sort of regex?

Any help is appreciated.

EDIT

The most important part is that some values may not have <code>;</code> and <code>:</code> so my query should also not fail might be some sort of check for this

SQL Server replace all values before a colon and after a semi-colon

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一篇专栏，写着Test。该测试具有asp:test;abca、asps:tesst;abcsa、asssp:tsest;assbca等值。我只对;之前和:之后的文本感兴趣，所以我的结果应该是test, tesst, tsest我只想用SQL查询来完成它，而不是在代码中编程。Server是否支持某种正则表达式？任何帮...

问Server替换冒号之前和分号之后的所有值
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server替换冒号之前和分号之后的所有值EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server替换冒号之前和分号之后的所有值
EN