在SQL Server 2008中通过regex删除特定文本的特定字符(Remove specific characters for a specific text via regex in SQL

在SQL Server 2008中通过regex删除特定文本的特定字符(Remove specific characters for a specific text via regex in SQL Server 2008)

我正在使用SQL Server 2008。

数据：

AA00012345/99 AX0000045687044/78 XB000077008/12

我需要做的是仅使用REGEX从输出中的字符串开始删除连续的零 （在看到0除外的数字后，允许连续的零）和斜杠（/）字符 。

预期产出：

AA1234599 AX4568704478 XB7700812

我设法通过这个查询来完成它：

select dbo.RegexGroup(t.Value,'(?<prefix>\w\w)(?<zeros>0*)(?<beforeslash>\d*)/(?<afterslash>\d\d)', 'prefix') + dbo.RegexGroup(t.Value,'(?<prefix>\w\w)(?<zeros>0*)(?<beforeslash>\d*)/(?<afterslash>\d\d)', 'beforeslash') + dbo.RegexGroup(t.Value,'(?<prefix>\w\w)(?<zeros>0*)(?<beforeslash>\d*)/(?<afterslash>\d\d)', 'afterslash') from table t

不过，我相信应该有更好更专业的方式来处理这个问题。 正则表达式的使用是必须的。 任何意见，将不胜感激。

I am using SQL Server 2008.

Data:

AA00012345/99 AX0000045687044/78 XB000077008/12

What I need to do is to remove consecutive zeros at the beginning only (after a number except 0 is seen, consecutive zeros are allowed) and the slash (/) chars from the string in the output by using REGEX.

Expected output:

AA1234599 AX4568704478 XB7700812

I managed to do it via this query:

select dbo.RegexGroup(t.Value,'(?<prefix>\w\w)(?<zeros>0*)(?<beforeslash>\d*)/(?<afterslash>\d\d)', 'prefix') + dbo.RegexGroup(t.Value,'(?<prefix>\w\w)(?<zeros>0*)(?<beforeslash>\d*)/(?<afterslash>\d\d)', 'beforeslash') + dbo.RegexGroup(t.Value,'(?<prefix>\w\w)(?<zeros>0*)(?<beforeslash>\d*)/(?<afterslash>\d\d)', 'afterslash') from table t

However, I believe there should be a better and more professional way to handle this issue. Regex usage is a must. Any advice would be appreciated.

最满意答案

这里有一个选项。 查询将字符串分为两部分，第一个字母，第二个休息部分，以数字开头。 通过替换空格并应用ltrim函数来删除0

declare @t table (c varchar(100)) insert into @t values ('AA00012345/99') ,('AX0000045687044/78') ,('XB000077008/12') select t.c, q2.p1 + replace(replace(ltrim(replace(q2.p2, '0', ' ')), ' ', '0'), '/', '') from @t t cross apply (select ci = patindex('%[0-9]%', t.c)) q1 cross apply (select p1 = substring(t.c, 1, q1.ci - 1), p2 = substring(t.c, q1.ci, len(t.c))) q2

输出：

AA00012345/99 AA12345/99 AX0000045687044/78 AX45687044/78 XB000077008/12 XB77008/12

Here's one option. Query divides strings into two parts, first - letters, second - rest part, starting with number. 0 is removed by replacing with space and applying ltrim function

declare @t table (c varchar(100)) insert into @t values ('AA00012345/99') ,('AX0000045687044/78') ,('XB000077008/12') select t.c, q2.p1 + replace(replace(ltrim(replace(q2.p2, '0', ' ')), ' ', '0'), '/', '') from @t t cross apply (select ci = patindex('%[0-9]%', t.c)) q1 cross apply (select p1 = substring(t.c, 1, q1.ci - 1), p2 = substring(t.c, q1.ci, len(t.c))) q2

Output:

AA00012345/99 AA12345/99 AX0000045687044/78 AX45687044/78 XB000077008/12 XB77008/12