Replace text in value witout knowing the next character

Question

I am trying to replace text within one of my SQL columns. The text to replace can easily be found by finding all references of /*REPLACE*/ with the next character being a numeric value which must be jacked up by 5000.

An example of the original value is displayed below.

var checkIn = moment(FIELD(/*REPLACE*/4,/*REPLACE*/9).GET());  
var checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET());    
if (checkIn > checkOut) {      
    FIELD(/*REPLACE*/4,/*REPLACE*/10).SET(checkIn.format("DD MMM YYYY"));      
    checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET());  
}    
FIELD(/*REPLACE*/4,/*REPLACE*/11).SET(parseFloat(checkOut.diff(checkIn, "days")).toFixed(2));

I need to find all references of /*REPLACE*/ and increase the following character which is numeric by 5000.

An example of the new values is displayed below.

var checkIn = moment(FIELD(5004,5009).GET());  
var checkOut = moment(FIELD(5004,5010).GET());    
if (checkIn > checkOut) {      
FIELD(5004,5010).SET(checkIn.format("DD MMM YYYY"));      
checkOut = moment(FIELD(5004,5010).GET());  
}    
FIELD(5004,5011).SET(parseFloat(checkOut.diff(checkIn, "days")).toFixed(2));

I have started with the basic but got lost with finding the numeric character after the text.

SELECT column.REPLACE(column, '/*REPLACE*/',

Any help please?

Answer 1

Assuming your column is of a varchar or similar type (not text), you can preform that replacement with a T-SQL script.

You will need to iterate each /REPLACE/ string, then have another nested loop to search for the next folowing digits. Once you know where the last folowing digit is, you can convert those digits to a number, and replace the desired string with that number + 5000.

I'm posting you an example. You can run this script right away to test it, but you need to adapt it to you table/column.

Also, if your column is of the type Text, it is also possible to do the same, but it will require splitting the text into varchar chunks and iterating each chunk. Let me know if you need help with that.

Here is the code:

-- table variable, simulating the table to be modified, for testing purposes
-- all references to @tbl and col should be replaced by the intended table/column
declare @tbl table (col varchar(max))
insert into @tbl values ('var checkIn = moment(FIELD(/*REPLACE*/4,/*REPLACE*/9).GET());  
var checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET());    
if (checkIn > checkOut) {      
    FIELD(/*REPLACE*/4,/*REPLACE*/10).SET(checkIn.format("DD MMM YYYY"));      
    checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET());  
}    
FIELD(/*REPLACE*/4,/*REPLACE*/11).SET(parseFloat(checkOut.diff(checkIn, "days")).toFixed(2));')

declare @col_txt varchar(max),
        @replace_at int, @digit_start int, @digit_end int, @number int

select @col_txt = col from @tbl -- TODO add a where clause, and consider using a cursor for each row

set @replace_at = 0
-- loop for each /*REPLACE*/ string
while (1=1) begin
    set @replace_at = charindex('/*REPLACE*/', @col_txt, @replace_at + 1)
    if (@replace_at = 0) break

    set @digit_start = @replace_at + len('/*REPLACE*/')
    set @digit_end = @digit_start
    -- loop while the next char is a number, and save its position
    while (ascii(substring(@col_txt, @digit_end, 1)) between ascii(0) and ascii(9)) set @digit_end = @digit_end + 1
    set @number = cast(substring(@col_txt, @digit_start, @digit_end - @digit_start) as int) + 5000
    -- replace current /*REPLACE*/ and number with the found number + 5000 in the current text chunk
    set @col_txt = stuff(@col_txt, @replace_at, @digit_end - @replace_at, @number)
end

-- update the original table with the value
update @tbl set col = @col_txt -- TODO add a where clause

-- only for test purposes
select col from @tbl