Select with replace..... - SQL Server Forums

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)

Select with replace.....

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-07-28 : 19:25:26

I have a table that looks like this:
ID | AdCode | URL

I want to do a select, where if URL is NULL then I just return the AdCode, if it isn't NULL then I want the select to parse the AdCode for the string URL and replace it with the following:

@ClickURL = 'http://www.somewhere.com/tracker.asp?' + ID

where ID is the ID of the select

so if the table that has a record such as:

1|'click here <A href="http://thislink.com">now</a>'|'http://thislink.com'

and I do the select it would return the following for the AdCode:

click here <A href="http://www.somewhere.com/tracker.asp?1">now</a>

Does that make sense? I've been struggling with it for a few hours and can't seem to make any head way. Seems like it should be easy enough, but it's killing me.

Thanks everyone!

Scott.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-28 : 19:33:54

How about this:


SELECT 'AdCode' = 
      CASE 
        WHEN URL IS NULL THEN AdCode
        ELSE '@ClickURL = ''http://www.somewhere.com/tracker.asp?' + CONVERT(VARCHAR(50), [ID])
      END
FROM TABLE1

Tara

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-07-28 : 20:06:09

I think that's close, but I need to search and replace for the URL in AdCode with the tracking URL. IF URL isn't null, then I want to replace the URL in AdCode (will be the same URL in the URL field) with the tracking URL which I need to define in the stored proc.

So If URL is not null then it should do something like this
--Define Tracking URL
@ClickURL = = 'http://www.somewhere.com/tracker.asp?'
--Do the replace and select
select (Replace(AdCode, URL, @ClickURL + ID) as AdCode from Table1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-28 : 20:09:15

I am just not understanding then. Could you provide some sample data in this format:

INSERT INTO Table1 (AdCode, URL)
SELECT 'SomeValue', 'SomeURL'

INSERT INTO Table1 (AdCode, URL)
SELECT 'SomeOtherValue', 'SomeOtherURL'

Then provide what the result set should look like.

Tara

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-07-28 : 20:11:50

Do you need to update the column or just to return the changed value in your select?

Sérgio

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-07-28 : 20:13:50

I just need to return the value, I don't need to update the field....

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-07-28 : 20:26:33

Try it, together Tara's sugestion:

SELECT STUFF('Click Here <A href="http://thislink.com.br">now<',
charindex('<', 'Click Here <A href="http://thislink.com.br">now<'), charindex('>', 'Click Here<A href="http://thislink.com.br">now<') - charindex('<', 'Click Here<A href="http://thislink.com.br">now<')+1,
'<A href="http://www.somewhere.com/tracker.asp?1">')

Sérgio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources