Insert into SQL server from Regular Expression ret

Question

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.

e.g. if Column1 had 'the first article #345 is refered by 9999 and place in 001', it will return 345#9999#001

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.

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.

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.

Answer 1

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 

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.