Replacing a portion of text string in column

I need to replace a portion of a url in a column as a result of
changing servers. Is there a SELECT/REPLACE/UPDATE combination query
that can do this. The table has close to a thousand entries and would
be nice if a query can be set to do this. Tried the REPLACE example
in the BOOKS ONLINE but it creates syntax error, apparently because it
does not like the characters in the url and/or wildcards. I don't need
to replace the entire url, only the portion before ".com". Thanks in
anticipation of your help.

Pradip SagdeoPradip,
This statement:
select replace('http://www.technicalvideos.net','s.net','s14.net')
seems to work OK, so, perhaps you could give your exact update and the exact
error along with some sample data.
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Pradip Sagdeo" <pradip.m.sagdeo@.pfizer.com> wrote in message
news:8dbc5a0f.0401280726.708a1c6@.posting.google.co m...
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.
> Pradip Sagdeo|||Thanks. I will try again. Must have made a typing mistake.

Pradip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pradip Sagdeo (pradip.m.sagdeo@.pfizer.com) writes:
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.

Unfortunately, the replace() function does not support wildcards, so
if you need to use that, you have to be creative. Or descend to use
some client code to handle it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

replace table with different datasource in dsv

HI all,

I've used test db which is the small portion of production db and now I'm trying to replace all tables with production db in dsv. But it gives an OLE DB error now. (but I checked it and nothing wrong with it.)

What should I check when replacing table with differenct db?

Do I check the data source references in ds?

Please give me some comments.

Thanks in advance.

If the table names and schema names (on which you built dimensions, cubes and partitions) are the same in the test and production databases, the only thing you need to change is the connection string of the DataSource object, to use production database as the default catalog.

Adrian Dumitrascu

Replace or Subtract string

I have 2 columns being pulled in from mssql and need only a portion of
one column listed, for example I have:
Col1 Col2
--
--
BUILTIN_Administrators_master BUILTIN_Administrators_
sa_master sa_
DBA_msdb DBA_
...etc
I would like to, for lack of a better word, subtract column 2 from 1 to
leave "master", "msdb", etc.
Is this possible with SRS, and if so how?
Apparently I am new to this and not very skilled with the programming
aspect either, still learning. So any help would be appreciated.Got it using:
=Replace( Fields!path_leaf.Value, Fields!New.Value,"")sql

Replace data portion of a column

Hi all. I have a table, with information that complies with a condition
similar to this:
Select
*
From dbo.Object
Where
Name Like '%triplle%'
And I need to replace, for all those rows, in the column [Name], the word
'Triplle' with 'Triple', and leave the rest of the characters in [Name],
intact.
For example:
'This is an example triplle' convert to 'This is an example triple'
'Another triplle here' convert to 'Another triple here'
'and triplle here also' convert to 'and triple here also'
Problem is, that [Name] is of type NText, and Replace doesn't work with
NText. So far, I'm using this, which would be a problem, if the text
contained in [Name], is greater than 3000 (or any other number that I use):
Update dbo.Object
Set
Name = Replace( Convert( NChar( 3000 ), Valor ), 'triplle', 'triple' )
Where
Name Like '%triplle%'
I came up with another solution, which looks too complicated (but it works),
by using TextPtr, CharIndex, UpDateText. I have the idea that there should
be a simpler and better solution. Is there such?
Appreciate your help,
FrankCREATE TABLE testb ([name] ntext)
INSERT INTO testb VALUES ('This is an example triplle')
INSERT INTO testb VALUES ('Another triplle here')
INSERT INTO testb VALUES ('and triplle here also')
select substring([name], 0, PATINDEX('%triplle%',[name])) + 'triple' +
substring([name], PATINDEX('%triplle%',[name]) + 7,DATALENGTH([name]))
from testb
This will work if the word 'triplle' occurs only once for each value of the
ntext column. If it could appear multiple times, then encapsulate the above
within a WHILE loop to remove all occurrences.
--
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"John Francisco Williams" wrote:

> Hi all. I have a table, with information that complies with a condition
> similar to this:
> Select
> *
> From dbo.Object
> Where
> Name Like '%triplle%'
> And I need to replace, for all those rows, in the column [Name], the word
> 'Triplle' with 'Triple', and leave the rest of the characters in [Name],
> intact.
> For example:
> 'This is an example triplle' convert to 'This is an example triple'
> 'Another triplle here' convert to 'Another triple here'
> 'and triplle here also' convert to 'and triple here also'
> Problem is, that [Name] is of type NText, and Replace doesn't work with
> NText. So far, I'm using this, which would be a problem, if the text
> contained in [Name], is greater than 3000 (or any other number that I use):
> Update dbo.Object
> Set
> Name = Replace( Convert( NChar( 3000 ), Valor ), 'triplle', 'triple'
)
> Where
> Name Like '%triplle%'
> I came up with another solution, which looks too complicated (but it works
),
> by using TextPtr, CharIndex, UpDateText. I have the idea that there should
> be a simpler and better solution. Is there such?
> Appreciate your help,
> Frank
>
>