Extract ID from string within ntext field

Here is an example of how to update the table

CREATE TABLE test (i int identity(1,1), j int, t text, c char(2))
go


delete test


INSERT INTO Test SELECT 1, 'asadf', 'I'
INSERT INTO Test SELECT 1, 'jjjjj', 'A'
INSERT INTO Test SELECT 1, 'kkkkksadf', 'A'
DECLARE @t varchar(8000)
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'

INSERT INTO Test SELECT 2, 'asadf', 'I'
INSERT INTO Test SELECT 2, 'jjjjj', 'A'
INSERT INTO Test SELECT 2, 'kkkkksadf', 'A'
--DECLARE @t varchar(8000)
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'






DECLARE @old varchar(20) ,
@new varchar(20)
SELECT @old = 'adf' ,
@new = 'qsd'

DECLARE @i int

DECLARE @txtPtr binary(16) ,
@offset int ,
@dellen int

SELECT @dellen = len(@old)
SELECT @txtPtr = 0

WHILE @txtPtr IS NOT NULL
BEGIN
SELECT @txtPtr = null
SELECT TOP 1 @i = i, @txtPtr = textptr(t)
FROM test
WHERE t LIKE '%' + @old + '%'

IF @txtPtr IS NOT NULL
BEGIN
SELECT @offset = 1
WHILE @offset <> 0
BEGIN
SELECT @offset = patindex('%' + @old + '%', t)
FROM test
WHERE i = @i
--SELECT offset = @offset

IF @offset > 0
BEGIN
SELECT @offset = @offset-1
UPDATETEXT test.t @txtPtr @offset @dellen @new
END
END -- end while
END-- End if
END-- end while

>>Can anyone give me a pointer as to how to find the url within the text and extract the old id from it?

Funny that you should say pointer, because that is the way to update text in a ntext (or text) column. Text columns are different than varchar/nvarchar columns. Maybe you can use a CAST or CONVERT to varchar and then do a search (much easier).

First - find the datalength of the column
SELECT datalength(notes) FROM table

If it is under 8,000 (varchar) or 4,000 (nvarchar) then you can CAST or CONVERT to either to do the search. If not, then you have to use the process aneeshattingal describes.

HI, Thanks for the comments.  I can replace the text no problem.  My problem is extracting the id from the text i want to replace.  

i.e. if the string is 'default.asp?id=1234' where the id can be a 1 - 5 digit number, i need to be able find the string, get hold of the id (1234) and replace that part with a new id stored in another table.  

my steps would be:
1. find the string 'default.asp?id=1234'
2. select newID from othertable where oldID = 1234
3. replace the string with 'default.aspx?id=[the new id from step 2]

cheers

DECLARE @SubStr nvarchar(4000)
DECLARE @ID int
SELECT @SubStr = SUBSTRING(urTextColumn, PATINDEX('%http://127.0.0.1/default.asp?id=1234%',urTextColumn), Len('http://127.0.0.1/default.asp?id=1234'))
FROM urTable

SET @SubStr = SUBSTRING(@SubStr,CHARINDEX('=',@SubStr),LEN(@SubStr) )

SET @ID  = CAST(@SubStr as INT)

Hi,  that's what I'm trying to achieve, the problem is i don't know the id. My starting string could be http://127.0.0.1/default.asp?id=  followed by any 1 to 5 digit number.  i.e. the id could be 1234  or 34 or 50001.  what i need is a way to parse out the number after the '=' in the string

cheers

Will that be the last portion of that text, or something else is there

it will be followed by

">