Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Extract ID from string within ntext field

I have an ntext field in a database that contains a page of html.  I need to search within the text for every occurrence of a link in the form: “http://127.0.0.1/default.asp?id=1234” and replace it with “http://127.0.0.1/default.aspx?id=NewID”.  
i.e. I need to find the occurrence of the url, extract the id, perform a lookup on a table that maps the old ids to the new ones and replace it.  

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

cheers
0
APhoenixRising
Asked:
APhoenixRising
  • 4
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0
 
ptjcbCommented:
>>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.
0
 
APhoenixRisingAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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)
0
 
APhoenixRisingAuthor Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Will that be the last portion of that text, or something else is there
0
 
APhoenixRisingAuthor Commented:
it will be followed by

">

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I hope it is a double Quote



DECLARE @SubStr nvarchar(4000)
DECLARE @ID int
SELECT @SubStr = SUBSTRING(urTextColumn, PATINDEX('%http://127.0.0.1/default.asp?ID=%',urTextColumn),
CHARINDEX('"',urTextColumn,PATINDEX('%http://127.0.0.1/default.asp?ID=%',urTextColumn))+1  -PATINDEX('%http://127.0.0.1/default.asp?ID=%',urTextColumn)
)
FROM urTable

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

SET @ID  = CAST(@SubStr as INT)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now