我正在使用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/12What 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 XB7700812I 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 tHowever, 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/12Here'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))) q2Output:
AA00012345/99 AA12345/99 AX0000045687044/78 AX45687044/78 XB000077008/12 XB77008/12更多推荐
发布评论