从正则表达式返回的文本插入SQL Server [英] Insert into SQL server from Regular Expression returned text
问题描述
在SQL Server 2008中,我从下面的代码项目站点构建了一个正则表达式匹配和替换功能,并且运行良好. http://www.codeproject.com/KB/string/SqlRegEx .aspx?msg = 3683405#xx3683405xx . 此功能基本上是查找列文本,找到匹配项并替换为替换的文本.我在这里使用过回溯引用.
In SQL Server 2008, I have built a regex match and replace function from below code project site and is working well. http://www.codeproject.com/KB/string/SqlRegEx.aspx?msg=3683405#xx3683405xx. This functions basically looks up column text, finds match and replaces with the replaced text. I have used back reference here.
例如如果Column1中有第一篇文章#345被9999引用并放在001中",它将返回 345#9999#001
e.g. if Column1 had 'the first article #345 is refered by 9999 and place in 001', it will return 345#9999#001
select语句 选择column1,dbo.ufn_RegExReplace(Column1,'(?\ d +).?(?\ d +).?(?\ d +).*?(?\ d +)','$ {First_number_match }#$ {Second_number_match}#Third_number_match',1)工作正常.
The select statement Select column1, dbo.ufn_RegExReplace(Column1, '(?\d+).?(?\d+).?(?\d+).*?(?\d+)', '${First_number_match}#${Second_number_match}#Third_number_match',1) is working fine.
我要在表格的三列中插入345#9999#001.
What I want is to insert 345#9999#001 into three columns of a table.
请注意,在我的实际问题中,我将不得不使用正则表达式.我简化了,让专家专注于这个问题.
Please note, in the my actual problem, I will have to use Regular Expression. I simplified for experts to focus on the issue.
我们知道Regex令人费解,并且与SQL一起使用增加了它.因此,我将对此表示感谢. 谢谢您阅读本文.
As we know Regex are nerve-racking and using with SQL adds to it. So I will appreciate any help on this. Thanks for your time to read this.
推荐答案
您可以创建一个字符串拆分函数,并使用该函数获取您的各个值.例如,如下所示:
You could create a string split function and use that to get your individual values. For example, something like this:
CREATE FUNCTION dbo.fn_StringSplit(@String NVARCHAR(8000), @Delimiter NCHAR(1))
RETURNS @tblItems TABLE (item NVARCHAR(8000))
AS
BEGIN
DECLARE @idx INT
DECLARE @slice NVARCHAR(8000)
SELECT @idx = 1
IF LEN(@String) < 1 OR @String IS NULL RETURN
WHILE (@idx != 0 AND LEN(@string) > 0)
BEGIN
SET @idx = CHARINDEX(@Delimiter, @String)
IF @idx != 0
SET @slice = LEFT(@String, @idx - 1)
ELSE
SET @slice = @String
IF(LEN(@slice) > 0)
INSERT INTO @tblItems(item) VALUES (@slice)
-- Trim saved item from remaining string
SET @String = RIGHT(@String, LEN(@String) - @idx)
END
RETURN
END
或者,您可以修改上面的Split函数,以根据需要在单个表行中返回3个值,或者在存储过程中作为3个输出参数返回.
Alternatively, you could modify the above Split function to return your 3 values in a single table row if desired, or as 3 output parameters on a stored procedure.
这篇关于从正则表达式返回的文本插入SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!