SQL Replace numbers in text

Question

I'm looking for a way to achieve replacements in a string.

Let's say I have a text which is (([343]+([509])*[1584]))/25. I want each number that exists in this text (which is enclosed in brackets) be updated with a value plus 2000.

So, this (([343]+([509])*[1584]))/25 should become this (([2343]+([2509])*[3584]))/25

I should do this via MS SQL. Could anyone get me started?

This kind of thing is better to do in a programming language then in t-sql. A pure t-sql solution is possible, but it will be a cumbersome code and not very efficient. — Zohar Peled
– Zohar Peled, Commented Jun 6, 2016 at 13:43
If you have to do it "via MS Sql", this might be a good time to use CLR. — Tab Alleman
– Tab Alleman, Commented Jun 6, 2016 at 13:47

gofr1 · Accepted Answer · 2016-06-06 14:07:18Z

3

DECLARE @val VARCHAR(100) = '(([343]+([509])*[1584]))/25', 
        @xml xml

SELECT @xml = CAST('<a>'+REPLACE(REPLACE(@val,']','</a><a>'),'[','</a><a>')+'</a>' as xml)

SELECT @val = CAST ((
SELECT CASE WHEN try_cast(t.v.value('.','nvarchar(max)') as int) is not null 
            THEN QUOTENAME(try_cast(t.v.value('.','nvarchar(max)') as int)+2000) 
            ELSE t.v.value('.','nvarchar(max)') END
FROM @xml.nodes('/a') as t(v)
FOR XML PATH('')) as nvarchar(max))

SELECT @val

Output:

(([2343]+([2509])*[3584]))/25

answered Jun 6, 2016 at 14:07

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2016-06-06 13:51:35Z

2

DECLARE @val VARCHAR(100) = '(([343]+([509])*[1584]))/25'

SELECT STUFF((
SELECT '[' + ISNULL(REPLACE(val, token, token + 2000), val)
FROM (
    SELECT token = PARSENAME(REPLACE(t.val, ']', '.'), 2), *
    FROM (
        SELECT val = t.c.value('(./text())[1]', 'VARCHAR(100)')
        FROM ( 
            SELECT x = CONVERT(XML, '<i>' + REPLACE(@val, '[', '</i><i>') + '</i>').query('.')
        ) a
        CROSS APPLY x.nodes('i') t(c)
    ) t
) t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

answered Jun 6, 2016 at 13:51

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Horaciux Over a year ago

I would use varchar(max) just in case, to avoid a truncated string

Ivan Sivak · Accepted Answer · 2016-06-06 14:23:28Z

An alternative using a user defined scalar function:

CREATE FUNCTION [dbo].[fnReplaceNumbers]
(
    @string varchar(max),
    @valueToAdd int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    declare @i int
    declare @len int
    declare @char varchar(1);
    declare @inToken bit;
    declare @num varchar(32);
    declare @result varchar(max);

    set @i = 1
    set @len = LEN(@string);
    set @inToken = 0;
    set @num = '';
    set @result = '';

    declare @table table(x varchar(128));


    WHILE @i <= @len
    BEGIN
        set @char = SUBSTRING(@string, @i, 1);

        if (@char = ']') set @inToken = 0;

        if (@inToken = 1) begin
            set @num = @num + @char;
        end else if (@inToken = 0) begin
            if (len(@num) > 0) begin
                set @result = @result + cast((cast(@num as int) + @valueToAdd) as varchar(max));
                set @num = '';
            end;
            set @result = @result + @char;
        end;

        if (@char = '[') set @inToken = 1;


        set @i = @i + 1;
    END

    return @result;
END

..and then just call it as follows:

select dbo.fnReplaceNumbers('(([343]+([509])*[1584]))/25', 2000)

Collectives™ on Stack Overflow

SQL Replace numbers in text

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related