Replace( column, 'patern', 'string')

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Replace( column, 'patern', 'string')

Author

Topic

Jenda
Starting Member

29 Posts

Posted - 2008-06-10 : 10:21:06

I need to find and replace things like 'http://some.site.com/viewJob.asp?id=[0-9]+-[0-9]+-[0-9]+' and replace them by the same URL with the correct numbers. The catch is that the numbers do not have always the same length and I don't know for sure what character will be after the last of the three numbers so searching for the 'http://some.site.com/viewJob.asp?id=' and then for a space or something is not going to cut it. Any clever ideas?

It's not so hard to find the few and far between rows that I need to fix
Value like '%http://some.site.com/viewJob.asp?id=%'
and Value not like '%http://some.site.com/viewJob.asp?id='+@theRightNumbers+'%'
cuts it fine, but the replacing is problematic.

Thanks, Jenda
(Working with strings without regexps is like chopping trees with a pocketknife.)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 10:40:35

You can always use '?id=' and +4 for position to replace.

E 12°55'05.25"
N 56°04'39.16"

Jenda
Starting Member

29 Posts

Posted - 2008-06-10 : 18:32:04

As I said I don't know how long are those three numbers. Or rather, for some time the first number is and will have 6 digits, the second may have 1 to 4 digits and the last 2 to 6 digits.

On the other hand ... I don't need to worry about URLs with more dash separated groups of digits so something like this:

Declare @text varchar(500), @UrlStatic varchar(200), @UrlGood varchar(200), @TextAfter varchar(500);

set @text = 'sdf werg sdfg wegrsdfg sdfg http://some.site.com/viewJob.asp?id=478-17-7187 and so forth'

set @UrlStatic = 'http://some.site.com/viewJob.asp?id=';
set @UrlGood = 'http://some.site.com/viewJob.asp?id=124-789-456';

select substring(@text, 1, patindex( '%'+@UrlStatic+'%', @text)-1)

set @TextAfter = substring(@text, patindex( '%'+@UrlStatic+'%', @text)+len(@UrlStatic), 8000)
select @TextAfter

set @Text = substring(@text, 1, patindex( '%'+@UrlStatic+'%', @text)-1)
+ @UrlGood
+ substring(@TextAfter, patindex( '%[^0-9-]%', @TextAfter), 8000);

select @Text

will work. I'll wrap it up in an UDF and be done :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources