Rendering report: string

Showing posts with label string. Show all posts

Friday, March 30, 2012

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||thanks but with '+COALESCE('Editions: ' +@.edition, '') since
'Editions' is hard coded even if @.edition is null
i get the Editions in the output
how do i make that null if @.edition is null as well|||My guess is that @.edition is '' which is not the same as NULL.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147881891.316429.181680@.y43g2000cwc.googlegroups.com...
> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>|||Actually no. A NULL value concatenated with any string will result in NULL.
So 'Editions: ' + NULL will yield NULL, instead of 'Editions: '.
The following example shows the result:
declare @.edition varchar(30)
set @.edition = null
select COALESCE('Editions: ' +@.edition, '') -- returns empty string ''
set @.edition = 'foo'
select COALESCE('Editions: ' +@.edition, '') -- returns "Editions: foo"
"VJ" wrote:

> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:

> I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:
> I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>

Replacing data in a string

I have a table with unique data in but it has been inputted, below is an example of the data

6331245073
638 535 0797
7023593578
ID Number 650 379 4428
OCA8 9F 32
ocb92w153
vh235704

I need to strip out the all letters and spaces so it will look like this:

6331245073
6385350797
7023593578
6503794428

ect....

Thanks

Rich

Hi Rich,

You need to create a scalar function to do this. Here is an example:
CREATE FUNCTION FormatString(@.input VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@.character CHAR(1),
@.newstring VARCHAR(100),
@.counter INT

SET @.counter = 0
SET @.newstring = ''

WHILE @.counter < LEN(@.input)
BEGIN
SET @.counter = @.counter + 1
SET @.character = SUBSTRING(@.input, @.counter, 1)

IF @.character >= '0' AND @.character <= '9'
SET @.newstring = @.newstring + @.character
END

RETURN @.newstring
END

You can call the function as follow (note the dbo. prefix):

SELECT dbo.FormatString('ID Number 650 379 4428' )
SELECT dbo.FormatString(fieldname) FROM tablename

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thanks Geert did the job

Rich

Wednesday, March 28, 2012

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way to
do this. Below is an example with column Col1 with 2 rows of data:
Col1
---
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.MittyKom,
Try the T-SQL REPLACE function.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:8F17586A-F6F6-46D1-A47D-5C85F3D36E9D@.microsoft.com...
> Hi All
> I have table Tab1 with a column Col1 of data type VARCHAR. I want to
> replace every "Kop " string in that column with "HY". Is there an easy way
> to
> do this. Below is an example with column Col1 with 2 rows of data:
> Col1
> ---
> J Dixion
> K Kop
> I got some helpful scripts on replacing a string within a TEXT column last
> time like the one below. I have tried those scripts on other data types of
> data like VARCHAR and it did not work.
> http://aspfaq.com/show.asp?id=2445
> Thank you in advance.
>

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way to
do this. Below is an example with column Col1 with 2 rows of data:
Col1
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.
MittyKom,
Try the T-SQL REPLACE function.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:8F17586A-F6F6-46D1-A47D-5C85F3D36E9D@.microsoft.com...
> Hi All
> I have table Tab1 with a column Col1 of data type VARCHAR. I want to
> replace every "Kop " string in that column with "HY". Is there an easy way
> to
> do this. Below is an example with column Col1 with 2 rows of data:
> Col1
> J Dixion
> K Kop
> I got some helpful scripts on replacing a string within a TEXT column last
> time like the one below. I have tried those scripts on other data types of
> data like VARCHAR and it did not work.
> http://aspfaq.com/show.asp?id=2445
> Thank you in advance.
>

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way t
o
do this. Below is an example with column Col1 with 2 rows of data:
Col1
---
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.UPDATE tab1
SET col1 = REPLACE(col1,'Kop','HY')
WHERE col1 LIKE '%Kop%' ;
David Portas
SQL Server MVP
--|||Thank you Dave. It got the job. Thank you once again.
--
5 years experience with SQL Server 2000 and SAP BW/SEM
"David Portas" wrote:

> UPDATE tab1
> SET col1 = REPLACE(col1,'Kop','HY')
> WHERE col1 LIKE '%Kop%' ;
> --
> David Portas
> SQL Server MVP
> --
>
>

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way t
o
do this. Below is an example with column Col1 with 2 rows of data:
Col1
---
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.MittyKom,
Try the T-SQL REPLACE function.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:8F17586A-F6F6-46D1-A47D-5C85F3D36E9D@.microsoft.com...
> Hi All
> I have table Tab1 with a column Col1 of data type VARCHAR. I want to
> replace every "Kop " string in that column with "HY". Is there an easy way
> to
> do this. Below is an example with column Col1 with 2 rows of data:
> Col1
> ---
> J Dixion
> K Kop
> I got some helpful scripts on replacing a string within a TEXT column last
> time like the one below. I have tried those scripts on other data types of
> data like VARCHAR and it did not work.
> http://aspfaq.com/show.asp?id=2445
> Thank you in advance.
>

Replacing a text globally

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

and replace all of them with "abc" without affecting the databases an application.

Can u please help me with step by step guidance?

muralidaran rYou want to change the names of things without affecting an application that uses this data?
Slow down and think about what effects this will have.|||hi

thanks for your reply

the only change expected from application is the change of connection string.

Muralidaran r|||Your application doesn't do anything like

SELECT Field1 FROM xyzTable

?|||My application code never uses table name or column name.

it uses only stored procedure names.

muralidaran r|||I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

I rest my case.|||If your application calls stored procedure names have "xyz" in them, then there is no solution to your problem... When you rename the called procedures, the application will fail (because it will still try to use the old names that contain "xyz" instead of "abc").

-PatP|||due to company/product name change,

Can u please help me with step by step guidance?

Sure, learn how to code correctly|||Thank you guys. Now I will retype the question. try to understand better. Actually the coding was done by someone else and now i have to manage this. OK. try to give a solution.

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
content of all stored procedures

and replace all of them with "abc"

Can u please help me with step by step guidance?

muralidaran r|||It shouldn't be done IMO.
Users don't see database name(s), table names, column names or content of stored procedures. Users should only see data that you let them see.|||How to search for a string "xyz" in

database name,

table name,

column name,

content of all stored procedures

muralidaran|||I want to search for a string "xyz" in
...
and replace all of them with "abc"

I did listen and I responded appropriately. My advice is don't do it, simple as that.

As always you can chose to ignore my advice but I can assure you that others will respond in a similar fashion.|||Hi

Already i started changing the string by some manuall method which is tedious and hard.

at each and every stage i am checking the applications performance and accuracy.

I need help to speed up this.|||Speaking from a SQL2000 perspective, you need to check "name" in sysobjects for tablenames, view names, proc names, etc. You need to check text in syscomments for sproc content, etc, you need to check syscolumns for column names, etc.

I'm sure for every place you catch, there will be 2 you don't catch.

I'm with GeorgeV, DO NOT DO IT. Let sleeping dogs lay.

Have fun.|||Dear muralidaran_r,

Have you understood what the users here are telling you?
If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name.

Or are you asking how to search for and change data stored in the tables?|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.

....or, find another career|||"If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name"

Yes to modify all, is there any automated method, tool, procedure available.

Muralidaran r|||No, not that I know of.

Replacing a single quote

How can use the REPLACE function to replace a single quote in a string in
T-SQL? I have tried using double quotes, but cannot get it to work. I am
trying to replace all the single quotes in at text field with a question
mark. for instance:
REPLACE(myfieldwithquotes,"'",'?')
--
JasonUse two single quotes:
REPLACE(myfieldwithquotes,'''','?')
ML
http://milambda.blogspot.com/|||escape single quote with single quote
replace('''','?')
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"JasonDWilson" wrote:

> How can use the REPLACE function to replace a single quote in a string in
> T-SQL? I have tried using double quotes, but cannot get it to work. I am
> trying to replace all the single quotes in at text field with a question
> mark. for instance:
> REPLACE(myfieldwithquotes,"'",'?')
> --
> Jason|||That is so you can escape them
REPLACE(myfieldwithquotes, CHAR(39), CHAR(39)+CHAR(39))
or to replace with a blank space
REPLACE(myfieldwithquotes, CHAR(39), '')sql

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

Replacing a character

I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given character\string with another one in this column. Any assistance on this is highly appreciated.

Thanks!

Santhosh

Santhosh,

1) If the text in the column is not too long, you can cast ntext to nvarchar and use replace function.

2) check this link http://www.webpowersoftware.co.uk/FORUMS/ShowPost.aspx?PostID=336

rgds,

v r kumar

Replacements of substrings in strings

This is not a piece of cake as I thought.

Have to replace few characters with few other characters in the string.

Now, I am using nested Replace:

(Replace(Replace(MyString,'','UE'),'','OE')

This example is simplified, number of replacements is more than eight of them.

Is there any other, more elegant, way to do this in just one command?No, but if you find yourself doing the same REPLACE in several places in your code, you can roll it into a function for ease of programming and clarity.|||Please ealborate a little...first read the sticky at the top of the forum...but my guess is you need to fix a table in one shot...

I would write a view, unload it, then reload the table...

Just a guess though

I hate DB2 OS/390

Replacement Root Query string

anyone can tell me how it is used it in Reportviewer to render
drillthrough links in the same page. I have been experimenting for a
while. would like to know if anything i need to take care of
thanx
raviHere's a thread on this topic:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
> anyone can tell me how it is used it in Reportviewer to render
> drillthrough links in the same page. I have been experimenting for a
> while. would like to know if anything i need to take care of
> thanx
> ravi
>|||Building on the thread I linked below...
In the code behind the page that contains ReportViewer, you need to set the
ReplacementRoot property, something like this:
reportView.ReplacementRoot = Protocol +
Request.ServerVariables["SERVER_NAME"] + Port + Request.FilePath +
"?report=";
You also have to add a bit more code to strip out extra rc: parameters that
build up as the user clicks on links. I hope that makes sense.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uWnN6inHFHA.2620@.tk2msftngp13.phx.gbl...
> Here's a thread on this topic:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
> news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
>> anyone can tell me how it is used it in Reportviewer to render
>> drillthrough links in the same page. I have been experimenting for a
>> while. would like to know if anything i need to take care of
>> thanx
>> ravi
>|||Hello Jeff,
Thanks for the reply. And I will make sure I dont create multiple
threads of the same topic.
Sadly, the problem still exists. I havent had time to work much on the
interface for the past week or so. I tried all you have said and also
looked at your past posts regarding this topic. I am send the code I
have regarding this whole thing. If you can tell where the problem is i
will appreciate very much.
I have a treeview control, which uses the ListChildren method of RS web
service to get all the reports listed as a tree. When I click on a
report (or rather the selected node changes) the following event is
fired.
BEGIN SUB---
Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
TreeView1.SelectedIndexChange
Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
curnode = TreeView1.GetNodeFromIndex(e.NewNode)
Xflag = True
ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
ReportViewer1.ReportPath = curnode.ID
ReportViewer1.Toolbar = ReportViewer.multiState.True
ReportViewer1.Zoom = "95"
ReportViewer1.ReplacementRoot =Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
ReportViewer1.ReportPath =ReportViewer1.ReportPath.Substring(0,
ReportViewer1.ReportPath.IndexOf("&rc"))
End If
ReportViewer1.Visible = True
END SUB.--
And this is the code i use to populate the Treeview will loading the
default page
BEGIN SUB--
Private Sub LoadSampleReports()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
For Each item In rs.ListChildren("/PGR1", False)
If item.Type = ItemTypeEnum.Report Then
newNode = New Microsoft.Web.UI.WebControls.TreeNode
newNode.Text = item.Name
newNode.ID = item.Path
newNode.Type = item.Type.ToString
newNode.HoverStyle.CssText ="font-size:8pt;color:dimgray;font-family:MS Sans
Serif;font-weight:bold;"
newNode.DefaultStyle.CssText = "font:MS Sans
Serif;color:Indigo; background:white;font-size:8pt"
& "/default.aspx?/PGR1/Averiguaciones" &
"&rc:LinkTarget=Myframe"
newNode.Expanded = False
newNode.Expandable =Microsoft.Web.UI.WebControls.ExpandableValue.Auto
TreeView1.Nodes.Add(newNode)
End If
Next
End Sub
END SUB---
When I click on a drillthrough link, it is opening the default page
again but without the report.
and the following is the URL associated with a drillthrough link
--
http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
--
Reportdashboard is the Web app.
Thanks very much
Ravi|||Okay, here's a tangent. If you test the reports in the report manager, does
the drill through work correctly?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ravi R" <bofobofo@.yahoo.com> wrote in message
news:1109898353.744329.74340@.z14g2000cwz.googlegroups.com...
> Hello Jeff,
> Thanks for the reply. And I will make sure I dont create multiple
> threads of the same topic.
> Sadly, the problem still exists. I havent had time to work much on the
> interface for the past week or so. I tried all you have said and also
> looked at your past posts regarding this topic. I am send the code I
> have regarding this whole thing. If you can tell where the problem is i
> will appreciate very much.
> I have a treeview control, which uses the ListChildren method of RS web
> service to get all the reports listed as a tree. When I click on a
> report (or rather the selected node changes) the following event is
> fired.
> BEGIN SUB---
> Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
> e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
> TreeView1.SelectedIndexChange
> Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
> curnode = TreeView1.GetNodeFromIndex(e.NewNode)
> Xflag = True
> ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
> ReportViewer1.ReportPath = curnode.ID
> ReportViewer1.Toolbar = ReportViewer.multiState.True
> ReportViewer1.Zoom = "95"
> ReportViewer1.ReplacementRoot => Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
> If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
> ReportViewer1.ReportPath => ReportViewer1.ReportPath.Substring(0,
> ReportViewer1.ReportPath.IndexOf("&rc"))
> End If
> ReportViewer1.Visible = True
> END SUB.--
> And this is the code i use to populate the Treeview will loading the
> default page
> BEGIN SUB--
> Private Sub LoadSampleReports()
> Dim rs As New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
> For Each item In rs.ListChildren("/PGR1", False)
> If item.Type = ItemTypeEnum.Report Then
> newNode = New Microsoft.Web.UI.WebControls.TreeNode
> newNode.Text = item.Name
> newNode.ID = item.Path
> newNode.Type = item.Type.ToString
> newNode.HoverStyle.CssText => "font-size:8pt;color:dimgray;font-family:MS Sans
> Serif;font-weight:bold;"
> newNode.DefaultStyle.CssText = "font:MS Sans
> Serif;color:Indigo; background:white;font-size:8pt"
> & "/default.aspx?/PGR1/Averiguaciones" &
> "&rc:LinkTarget=Myframe"
> newNode.Expanded = False
> newNode.Expandable => Microsoft.Web.UI.WebControls.ExpandableValue.Auto
> TreeView1.Nodes.Add(newNode)
> End If
> Next
> End Sub
> END SUB---
> When I click on a drillthrough link, it is opening the default page
> again but without the report.
> and the following is the URL associated with a drillthrough link
> --
> http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
> --
> Reportdashboard is the Web app.
>
> Thanks very much
> Ravi
>|||Hello Jeff,
Yes the drillthrough and the drilldown both work fine in the
ReportManager.
Thanks
Ravi|||Hello Jeff,
The following are the URLs First one from the ReportManager where the
drillthrough works. And the second one from my web app.
The reportserver is running on another machine.
http://servename/Reports/Pages/Report.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252fservername%252fReports%252fPages%252fReport.aspx%253fServerUrl%253d
------
http://localhost/Reportdashboard/default.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fServerUrl%253d
------
Thanks
Ravisql

Replacement of values within a string

Hi,
I would like to find out what function I would need to
call to replace characters other than asterisks (*) in a
string value.
For example, I have values in a column in a table like
********SFWE, *****100****, *****200****, *****300****,
and *****400****. I now want these values to be
represented as ********YYYY, *****YYY****, *****YYY****,
*****YYY****, respectively. I want all non-asterisks
value in the string to be replaced with 'Y'.
Thank you in advance,
bpdeeTake a look at CharIndex...
Rick
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Rick,
Thank you for your response. Although, I would not know
other than it is a non-asterisk value that is in the
string that I need to replace with the value of 'Y'. It
could be any alphanumeric value and any combination of it
that is in the string that I need to replace. It could
also be anywhere in the string.
Thanks,
bpdee
>--Original Message--
>Take a look at CharIndex...
>Rick
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>
>.
>|||You probably want to do something like this. I've got the first part of the
replace working, you can probably figure out the second part. You might
want to put this into a function...
The last column in the example is what you're after; the others are so you
can follow what I'm doing.
declare @.myfield varchar(100)
set @.myfield = '*****100****'
select CHARINDEX('*', @.myfield),
PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), -- you might want to
put 100 Y's into the string at the start - depending on what you expect to
have in your field
STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Andre,
Thank you for your response. You are correct that the
second one is the one I need to solve my problem. I ran
the select statement against my database and it is
definitely closer to what I need. Although, the result is
actually the opposite of what I want. The result I got
is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
want to keep all asterisks and replace those in the string
value that is NOT an asterisk with 'Y'. How would I do
that using the select you sent me?
Thanks,
bpdee
>--Original Message--
>You probably want to do something like this. I've got
the first part of the
>replace working, you can probably figure out the second
part. You might
>want to put this into a function...
>The last column in the example is what you're after; the
others are so you
>can follow what I'm doing.
>declare @.myfield varchar(100)
>set @.myfield = '*****100****'
>select CHARINDEX('*', @.myfield),
> PATINDEX('%[^*]%', @.myfield)-1,
> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
you might want to
>put 100 Y's into the string at the start - depending on
what you expect to
>have in your field
> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
[^*]%', @.myfield)-1,
>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>Andre
>
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>
>.
>|||Hi,
You can probably use ASCII function to determine the ASCII value of *...
CREATE TABLE #temp
(a1 int, a2 char(1), a3 char(1))
DECLARE @.position int, @.string char(12)
SET @.position = 1
SET @.string = '********SFWE'
WHILE @.position <= DATALENGTH(@.string)
BEGIN
INSERT INTO #temp SELECT ASCII(SUBSTRING(@.string, @.position, 1)) a1,
CHAR(ASCII(SUBSTRING(@.string, @.position, 1))) a2, 'Y' a3
SET @.position = @.position + 1
END
SELECT a2,a3 FROM #temp
WHERE a1 NOT IN( 42, 44, 32)
Now I guess you can figure out, how you can replace a2 with a3 and transform
them into columns...
Thanks
GYK
"bpdee" wrote:
> Hi Rick,
> Thank you for your response. Although, I would not know
> other than it is a non-asterisk value that is in the
> string that I need to replace with the value of 'Y'. It
> could be any alphanumeric value and any combination of it
> that is in the string that I need to replace. It could
> also be anywhere in the string.
> Thanks,
> bpdee
>
> >--Original Message--
> >Take a look at CharIndex...
> >
> >Rick
> >
> >"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I would like to find out what function I would need to
> >> call to replace characters other than asterisks (*) in a
> >> string value.
> >>
> >> For example, I have values in a column in a table like
> >> ********SFWE, *****100****, *****200****, *****300****,
> >> and *****400****. I now want these values to be
> >> represented as ********YYYY, *****YYY****, *****YYY****,
> >> *****YYY****, respectively. I want all non-asterisks
> >> value in the string to be replaced with 'Y'.
> >>
> >> Thank you in advance,
> >> bpdee
> >
> >
> >.
> >
>|||Here is some code that should work...
You could make this into a sproc or a function...
DECLARE @.OrgCol varchar(100)
@.Count int,
@.NewCol varchar(100)
SELECT @.OrgCol = ColumnName FROM TableName
SET @.Count = 1
SET @.NewCol = ''
WHILE (@.Count < DATALENGTH(@.OrgCol))
BEGIN
IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
SET @.NewCol = @.NewCol + 'Y'
ELSE
SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
SET @.Count = @.Count + 1
END
-- Write your update statement here or return @.NewCol if a UDF
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry, I didn't read well enough. :)
While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYYY',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
>>--Original Message--
>>You probably want to do something like this. I've got
> the first part of the
>>replace working, you can probably figure out the second
> part. You might
>>want to put this into a function...
>>The last column in the example is what you're after; the
> others are so you
>>can follow what I'm doing.
>>declare @.myfield varchar(100)
>>set @.myfield = '*****100****'
>>select CHARINDEX('*', @.myfield),
>> PATINDEX('%[^*]%', @.myfield)-1,
>> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
> you might want to
>>put 100 Y's into the string at the start - depending on
> what you expect to
>>have in your field
>> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
> [^*]%', @.myfield)-1,
>>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>>Andre
>>
>>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>>
>>.|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:
> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of values within a string

While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYYY',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
> the first part of the
> part. You might
> others are so you
> you might want to
> what you expect to
> [^*]%', @.myfield)-1,
> message
|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:

> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of values within a string

While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYY
Y',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*
]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
>
> the first part of the
> part. You might
> others are so you
> you might want to
> what you expect to
> [^*]%', @.myfield)-1,
> message|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:

> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Monday, March 26, 2012

Replace string inside value

Hi all, i have a question, i'm making a report in visual studio, where
i have a textbox which i fill with a value from a database and i wanna
know how you can change certain strings withing the filled value...
example:
This is the expression for the textbox:
=First(Fields!MyFieldName.Value, "Parametri")
=Replace(Fields!MyFieldName.Value.ToString(), "date", "current date")
So, the thing is, i've filled my textbox with a value from a database,
which is in my case, the value is a simple sentence...which has a word
'date' in it...so my question is how can i search that value
(sentence) and find the word "date" and replace it with something, for
example, today's date..?
That means if my textbox value is "bla bla bla date bla bla bla", i
wanna be able to change the word "date" to today's date or any other
word...so that in the report preview instead of "date" it says today's
date...
THANX!On Jun 28, 4:23 am, ApeX <mmo...@.gmail.com> wrote:
> Hi all, i have a question, i'm making a report in visual studio, where
> i have a textbox which i fill with a value from a database and i wanna
> know how you can change certain strings withing the filled value...
> example:
> This is the expression for the textbox:
> =First(Fields!MyFieldName.Value, "Parametri")
> =Replace(Fields!MyFieldName.Value.ToString(), "date", "current date")
> So, the thing is, i've filled my textbox with a value from a database,
> which is in my case, the value is a simple sentence...which has a word
> 'date' in it...so my question is how can i search that value
> (sentence) and find the word "date" and replace it with something, for
> example, today's date..?
> That means if my textbox value is "bla bla bla date bla bla bla", i
> wanna be able to change the word "date" to today's date or any other
> word...so that in the report preview instead of "date" it says today's
> date...
> THANX!
What you have is pretty close to correct. You should be able to use
something like this:
=Replace(CStr(Fields!MyFieldName.Value), "date", "current date") -or-
if you want the actual date time, you could use:
=Replace(CStr(Fields!MyFieldName.Value), "date", CStr(Now()))
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Friday, March 23, 2012

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 on a text field.

I need to search for all occurances of particular string within a column on a table. The column has a data type of Text. It will not allow me to use the replace function on a Text field only on varchars or chars. Does anybody have any ideas of how I can do this?njjones,

Look at Full Text Indexing in Books Online (BOL).

If however you can guarantee that none of the fields exceed 8000 characters you could CAST the TEXT field to a VARCHAR(8000). ie.

REPLACE(CAST(yourtextcolumn AS VARCHAR(8000)),'ABC','DEF')

macka.|||I have done that however quite a lot of the fields I need to affect are greater than 8000 characters (hence using the text datatype). I wanted to run a query to find out how many were longer but you can't use Len on a text field either - is there an easy way of finding the character length of text in a text column?|||Chances are not many will be exactly 8000 in length, so the following query gives you a rough idea of how many are bigger than 8k, but truncating all fields to 8000 characters.

SELECT COUNT(*)
FROM yourtable
WHERE LEN((CAST(yourtextcolumn AS VARCHAR(8000)))) = 8000

macka.|||The following works - seems a little heavy handed for a replacement of a one line function, but any:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%25.224.8.30%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%25.224.8.30%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 11
'modconnect1.qinetiq.r.mil.uk'
select @.pos1 =
patindex('%25.224.8.30%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||njjones,

Did you ever get the 'replace' issue resolved in a Text field? I need to do a similar action, finding all the commas in a text field and replacing it with a semi-colon.

Thanks.|||The answer is above, however I have recopied and pasted it below and updated it so that it should work for , and ; - probably could have parameterised this and turned it in a user defined function but it is not something I have needed to do often enough to bother with:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%,%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%,%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 1
';'
select @.pos1 =
patindex('%,%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||Nicky, thanks. A couple of quick modifications and I had this working well for my table. I appreciate it.

RY

Friday, March 30, 2012

replacing nulls

replacing nulls

replacing nulls

Replacing data in a string

Wednesday, March 28, 2012

Replacing all occurrences of a string in a VARCHAR column?

Replacing all occurrences of a string in a VARCHAR column?

Replacing all occurrences of a string in a VARCHAR column?

Replacing all occurrences of a string in a VARCHAR column?

Replacing a text globally

Replacing a single quote

Replacing a portion of text string in column

Replacing a character

Replacements of substrings in strings

Replacement Root Query string

Replacement of values within a string

Replacement of values within a string

Replacement of values within a string

Monday, March 26, 2012

Replace string inside value

Friday, March 23, 2012

Replace or Subtract string

Replace on a text field.

Rendering report

Blog Archive

About Me