Replacing Nulls

I have a query that has serveral Left Joins. Obviously when there are no
results in the right tables a value of NULL is returned. When creating an
MS Reporting Services report, the tables seem to ignore the NULL values
(because they aren't recognized as real values I assume). How do I alter my
query to replace a NULL value with an actually value, such as 999 so I can
count the "NULL" results?
I appreciate any help.
Thankstry ISNULL function
ISNULL(<fieldname>, 999) AS SomeThing
"Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I have a query that has serveral Left Joins. Obviously when there are no
>results in the right tables a value of NULL is returned. When creating an
>MS Reporting Services report, the tables seem to ignore the NULL values
>(because they aren't recognized as real values I assume). How do I alter
>my query to replace a NULL value with an actually value, such as 999 so I
>can count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Thanks for the reply SharkSpeed. That makes sense, but where would I put
it?
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:eqqHn8e9FHA.1020@.TK2MSFTNGP15.phx.gbl...
> try ISNULL function
> ISNULL(<fieldname>, 999) AS SomeThing
>
> "Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
> yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>|||Hi Amon
Have you tried the ISNULL([ColumnName], [NewValue]) function?
Lucas
"Amon Borland" wrote:

> I have a query that has serveral Left Joins. Obviously when there are no
> results in the right tables a value of NULL is returned. When creating an
> MS Reporting Services report, the tables seem to ignore the NULL values
> (because they aren't recognized as real values I assume). How do I alter
my
> query to replace a NULL value with an actually value, such as 999 so I can
> count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Lucas, where would I use this at. In the Select or after the table in the
join?
Thanks
"Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote in
message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
> Hi Amon
> Have you tried the ISNULL([ColumnName], [NewValue]) function?
> Lucas
> "Amon Borland" wrote:
>|||in the Select I assume..
SELECT a, b, n FROM table LEFT JOIN SELECT x, y, z FROM table2 ON ...
becomes
SELECT a, b, ISNULL(n, 999) AS n FROM table LEFT JOIN SELECT x, y, ISNULL(z,
999) FROM table2 ...
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:eZMs3Mf9FHA.220@.TK2MSFTNGP14.phx.gbl...
> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
> in message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>|||You can use it on the Select part of your SQL Statement. For example:
SELECT [ColumnName1], ISNULL([ColumnName2], [NewValue])
FROM [TableName]
Lucas
"Amon Borland" wrote:

> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
in
> message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>
>

replacing Null values with 0's in Matrix

Hi,

I am newbie to reporting services and I need some help. Could any one please let me know how to replace Null value with 0 in the data section of the Matrix. I don't want blanks to be displayed on the report, I want those to be replaced with 0's.

Thanks for your time.

Use this expression:

IIf(Fields!YourField.Value = Nothing Or IsNothing(Fields!YourField.Value), 0, Fields!YourField.Value)

Shyam

|||Or you can get rid of the nulls at data level by using a coalesce or IfNull function on the field in the select statement.|||

Thanks a lot guys for the reply.

Sluggy, I did try to use the coaslesceempty or the isempty function to replace the null's with 0's at the data level. But somehow in reporting services when it rolls up a value containing null's it displays '#Error' in the preview tab although in the data tab it gives proper results. But it works perfectly on the analysis services browser and also in Excel. Any idea why the '#Error" occours in Reporting services. the code i am using is

Case

When IsEmpty([Measures].[Account Count])
Then Null

Else
COALESCEEMPTY(([Measures].[Debtor Count],
[Account].[Account Close Flag].&[False]), 0)
End

Thanks.

|||

RS will typically show #Error when you try to do an aggregate function on a list (or item) of data that contains a NULL, and i see with your IsEmpty statement you are setting it to null - set to zero instead.

Replacing NULL value in multiple columns in a table

Hi,

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

Thanks in Advance
BobBF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.
>
Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL
>
But because there are lots of columns this is pretty much work, also
there are multiple tables.
>
Is there an easy way to replace all NULL values in all columns in a
table?

First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.

No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:

SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Great,

Thanks a lot, with the query I can create the new script much and much
easier.

I replaced the o.name with the tables which I new they had the problem
and now I have have all columns to replace the values.

Thanks a lot.

Regards, Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

>
First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.
>
No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:
>
SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Replacing column value on insert

Hello SQLServer-specialists!
I'm running a database which contains on table, where the customer is
allowed to import data into.
This table also has a column, which the customer should set to his name, so
that you know who imported the record.
The problem is, that the customer is not restricted in setting this columns
value, so he also could set it to 'foo'!
Now I want to ensure, that the value is always set to the logged on user,
when importing data.
I know, that using a view and an INSTEAD OF trigger would do the job.
But as the table has abount 250 columns, I try to find an easier way.
Is it possible to write a trigger, which executes just before the insert,
and is able to replace a columns value?
Thanks in advance!
MaxHi there,
why not setting up a UPDATE / INSERT trigger then modifying the users
name, like this one below which takes the SUSER_SNAME as a input, make
sure you modify that, to eventually reflect your enviroment:
CREATE TRIGGER SomeTrigger ON SOmeTable
FOR INSERT
AS
BEGIN
UPDATE SomeTable SET ModifiedColumn = SUSER_SNAME
FROM SomeTable S
INNER JOIN INSERTED I
ON S.PKColumn = I.PKColumn
END
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--|||Markus,
You can use the system function SYSTEM_USER to get the current username.
You can create an AFTER Trigger for inserts and update the inserted rows
Username with the SYSTEM_USER function.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>|||Hello!
Thanks for your answers!
I now tried the following
CREATE TRIGGER ModificationTrigger ON TheTableToInsertTo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO TheTableToInsertTo
SELECT col1, col2, suser_sname()
FROM inserted
END
First I thought, that inserting into the same table, as the trigger should
instead of an insert, could make problems, but it works perfectly!!!
Greetings,
Max
"Markus Emayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>

Replacing a character

Hi

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

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

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
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
|||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...
>
>.
>
|||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,
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
>
> message
>
|||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...[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

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...
>
>.
>|||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 t
o
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,
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
>
> message
>|||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('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
>
>

REPLACE with NULL

Hello,
When I use the following:
REPLACE(CategoryType_ID, 'Non Selected', null)
all the values are replaced with NULL even when the value is not "'Non
Selected".
Any help with understanding why this happens would be appreciated.
Thanks, sck10The REPLACE function returns NULL if any one of the arguments is NULL. The
right function to do what you need is NULLIF:
NULLIF(CategoryType_ID, 'Non Selected')
You can also accomplish the same with CASE:
CASE WHEN CategoryType_ID = 'Non Selected'
THEN NULL
ELSE CategoryType_ID
END
I am assuming here the CategoryType_ID column is a character data type.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hi
"sck10" wrote:
> Hello,
> When I use the following:
> REPLACE(CategoryType_ID, 'Non Selected', null)
> all the values are replaced with NULL even when the value is not "'Non
> Selected".
> Any help with understanding why this happens would be appreciated.
> Thanks, sck10
If this is an update you can
UPDATE mYtable
SET CategoryType_ID = NULL
WHERE CategoryType_ID = 'Non Selected'
John|||Thanks Plamen Ratchev,
just what I was looking for...
sck10
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:uWahjljfHHA.4980@.TK2MSFTNGP02.phx.gbl...
> The REPLACE function returns NULL if any one of the arguments is NULL. The
> right function to do what you need is NULLIF:
> NULLIF(CategoryType_ID, 'Non Selected')
> You can also accomplish the same with CASE:
> CASE WHEN CategoryType_ID = 'Non Selected'
> THEN NULL
> ELSE CategoryType_ID
> END
> I am assuming here the CategoryType_ID column is a character data type.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||Thanks John,
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EAA2A779-0D99-4F93-A464-F855323CB61A@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> When I use the following:
>> REPLACE(CategoryType_ID, 'Non Selected', null)
>> all the values are replaced with NULL even when the value is not "'Non
>> Selected".
>> Any help with understanding why this happens would be appreciated.
>> Thanks, sck10
> If this is an update you can
> UPDATE mYtable
> SET CategoryType_ID = NULL
> WHERE CategoryType_ID = 'Non Selected'
> John
>sql

REPLACE with NULL

Hello,
When I use the following:
REPLACE(CategoryType_ID, 'Non Selected', null)
all the values are replaced with NULL even when the value is not "'Non
Selected".
Any help with understanding why this happens would be appreciated.
Thanks, sck10
The REPLACE function returns NULL if any one of the arguments is NULL. The
right function to do what you need is NULLIF:
NULLIF(CategoryType_ID, 'Non Selected')
You can also accomplish the same with CASE:
CASE WHEN CategoryType_ID = 'Non Selected'
THEN NULL
ELSE CategoryType_ID
END
I am assuming here the CategoryType_ID column is a character data type.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Hi
"sck10" wrote:

> Hello,
> When I use the following:
> REPLACE(CategoryType_ID, 'Non Selected', null)
> all the values are replaced with NULL even when the value is not "'Non
> Selected".
> Any help with understanding why this happens would be appreciated.
> Thanks, sck10
If this is an update you can
UPDATE mYtable
SET CategoryType_ID = NULL
WHERE CategoryType_ID = 'Non Selected'
John
|||Thanks Plamen Ratchev,
just what I was looking for...
sck10
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:uWahjljfHHA.4980@.TK2MSFTNGP02.phx.gbl...
> The REPLACE function returns NULL if any one of the arguments is NULL. The
> right function to do what you need is NULLIF:
> NULLIF(CategoryType_ID, 'Non Selected')
> You can also accomplish the same with CASE:
> CASE WHEN CategoryType_ID = 'Non Selected'
> THEN NULL
> ELSE CategoryType_ID
> END
> I am assuming here the CategoryType_ID column is a character data type.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
|||Thanks John,
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EAA2A779-0D99-4F93-A464-F855323CB61A@.microsoft.com...
> Hi
> "sck10" wrote:
>
> If this is an update you can
> UPDATE mYtable
> SET CategoryType_ID = NULL
> WHERE CategoryType_ID = 'Non Selected'
> John
>

REPLACE with NULL

Hello,
When I use the following:
REPLACE(CategoryType_ID, 'Non Selected', null)
all the values are replaced with NULL even when the value is not "'Non
Selected".
Any help with understanding why this happens would be appreciated.
Thanks, sck10The REPLACE function returns NULL if any one of the arguments is NULL. The
right function to do what you need is NULLIF:
NULLIF(CategoryType_ID, 'Non Selected')
You can also accomplish the same with CASE:
CASE WHEN CategoryType_ID = 'Non Selected'
THEN NULL
ELSE CategoryType_ID
END
I am assuming here the CategoryType_ID column is a character data type.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hi
"sck10" wrote:

> Hello,
> When I use the following:
> REPLACE(CategoryType_ID, 'Non Selected', null)
> all the values are replaced with NULL even when the value is not "'Non
> Selected".
> Any help with understanding why this happens would be appreciated.
> Thanks, sck10
If this is an update you can
UPDATE mYtable
SET CategoryType_ID = NULL
WHERE CategoryType_ID = 'Non Selected'
John|||Thanks Plamen Ratchev,
just what I was looking for...
sck10
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:uWahjljfHHA.4980@.TK2MSFTNGP02.phx.gbl...
> The REPLACE function returns NULL if any one of the arguments is NULL. The
> right function to do what you need is NULLIF:
> NULLIF(CategoryType_ID, 'Non Selected')
> You can also accomplish the same with CASE:
> CASE WHEN CategoryType_ID = 'Non Selected'
> THEN NULL
> ELSE CategoryType_ID
> END
> I am assuming here the CategoryType_ID column is a character data type.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||Thanks John,
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EAA2A779-0D99-4F93-A464-F855323CB61A@.microsoft.com...
> Hi
> "sck10" wrote:
>
> If this is an update you can
> UPDATE mYtable
> SET CategoryType_ID = NULL
> WHERE CategoryType_ID = 'Non Selected'
> John
>

Replace value with image

Hello, I'm new to Reporting Services 2005, so I'm not sure if what I'm asking
is possible. What I need to do is replace a certain value in a report with
an image. For example, the values in a given column will either be -1, 0 or
1. What I need to do is replace that value with a specific graphic, based on
which value it is. Can anyone let me know how this could be done or if it's
even possible?
Thank you!Hi,
You can do this by adding a conditional statement to the BackGroundImage
Property's value field
You can write
IIf(Fields!YourField.Value=-1,"Image-1",IIF(Fields!YourField.Value=0,"Image0",IIF(Fields!YourField.Value=1,"Image1","")
Let me know if this worked
Sumit Pilankar
"Paul Havel" <PaulHavel@.discussions.microsoft.com> wrote in message
news:28C27E9A-FA4E-47DD-B3D8-E47A41517539@.microsoft.com...
> Hello, I'm new to Reporting Services 2005, so I'm not sure if what I'm
> asking
> is possible. What I need to do is replace a certain value in a report
> with
> an image. For example, the values in a given column will either be -1, 0
> or
> 1. What I need to do is replace that value with a specific graphic, based
> on
> which value it is. Can anyone let me know how this could be done or if
> it's
> even possible?
> Thank you!|||Thanks for the response and getting me going in the right direction. What I
had to ultimately do was add an Image from the toolbox to the cell where I
wanted to display the graphics. Then in the properties for that cell, I
specified the Value as a conditional statement that displays a given graphic
based on the value of one of the columns in the query. Works as expected!
Thanks again!
"Sumit Pilankar" wrote:
> Hi,
> You can do this by adding a conditional statement to the BackGroundImage
> Property's value field
> You can write
> IIf(Fields!YourField.Value=-1,"Image-1",IIF(Fields!YourField.Value=0,"Image0",IIF(Fields!YourField.Value=1,"Image1","")
> Let me know if this worked
> Sumit Pilankar
>
> "Paul Havel" <PaulHavel@.discussions.microsoft.com> wrote in message
> news:28C27E9A-FA4E-47DD-B3D8-E47A41517539@.microsoft.com...
> > Hello, I'm new to Reporting Services 2005, so I'm not sure if what I'm
> > asking
> > is possible. What I need to do is replace a certain value in a report
> > with
> > an image. For example, the values in a given column will either be -1, 0
> > or
> > 1. What I need to do is replace that value with a specific graphic, based
> > on
> > which value it is. Can anyone let me know how this could be done or if
> > it's
> > even possible?
> >
> > Thank you!
>
>

replace value of empty element?

Hi,
In SQL 2005, I have a XML with an one element with an empty value like
so:
<root>
<test></test>
</root>
When I try and replace the value of (/root/test) it does not seem to
work:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
select @.xml
returns this:
<root>
<test></test>
</root>
If however the element has a value already like so:
<root>
<test>some old value here already</test>
</root>
and I re-rerun the statement above:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
select @.xml
returns this:
<root>
<test>test new value</test>
</root>
Is something wrong there?In the first case, when you use expression (/root/test/text())[1] you refer
to
something that doesn't exist (there is no text node under element test) and
therefore nothing gets updated.
What you need to do is insert a new text node inder element "test" like this
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('insert text{"test new value"} as first into (/root/test)[1]
')
select @.xml
I hope this helps.
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<doctorphan@.gmail.com> wrote in message
news:1177013246.440921.259810@.d57g2000hsg.googlegroups.com...
> Hi,
> In SQL 2005, I have a XML with an one element with an empty value like
> so:
> <root>
> <test></test>
> </root>
> When I try and replace the value of (/root/test) it does not seem to
> work:
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @.xml
> returns this:
> <root>
> <test></test>
> </root>
>
> If however the element has a value already like so:
> <root>
> <test>some old value here already</test>
> </root>
> and I re-rerun the statement above:
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @.xml
> returns this:
> <root>
> <test>test new value</test>
> </root>
> Is something wrong there?
>|||On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
<denis...@.online.microsoft.com> wrote:
> In the first case, when you use expression (/root/test/text())[1] you refe
r to
> something that doesn't exist (there is no text node under element test) an
d
> therefore nothing gets updated.
> What you need to do is insert a new text node inder element "test" like th
is
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('insert text{"test new value"} as first into (/root/test)[
1] ')
> select @.xml
> I hope this helps.
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified athttp:/
/www.microsoft.com/info/cpyright.htm
> <doctorp...@.gmail.com> wrote in message
> news:1177013246.440921.259810@.d57g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
So I guess there is no one built-in statement that will work in both
scenarios? ie. One that will insert the text if one doesn't exist, and
one that will replace the text if it already exists. I guess I will
have to write a function?|||You should probably use the XML datatype's exist() method to determin if the
re
is already a text node or not and then perform the proper action depending
on
what case you're in.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
> So I guess there is no one built-in statement that will work in both
> scenarios? ie. One that will insert the text if one doesn't exist, and
> one that will replace the text if it already exists. I guess I will
> have to write a function?
>|||Try the following:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
set @.xml.modify('insert text{ "test
new value"} into (/root/test[not(text())])[1]')
select @.xml
Best regards
Michael
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
> So I guess there is no one built-in statement that will work in both
> scenarios? ie. One that will insert the text if one doesn't exist, and
> one that will replace the text if it already exists. I guess I will
> have to write a function?
>

replace value of empty element?

Hi,
In SQL 2005, I have a XML with an one element with an empty value like
so:
<root>
<test></test>
</root>
When I try and replace the value of (/root/test) it does not seem to
work:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
select @.xml
returns this:
<root>
<test></test>
</root>
If however the element has a value already like so:
<root>
<test>some old value here already</test>
</root>
and I re-rerun the statement above:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
select @.xml
returns this:
<root>
<test>test new value</test>
</root>
Is something wrong there?
In the first case, when you use expression (/root/test/text())[1] you refer to
something that doesn't exist (there is no text node under element test) and
therefore nothing gets updated.
What you need to do is insert a new text node inder element "test" like this
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('insert text{"test new value"} as first into (/root/test)[1] ')
select @.xml
I hope this helps.
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<doctorphan@.gmail.com> wrote in message
news:1177013246.440921.259810@.d57g2000hsg.googlegr oups.com...
> Hi,
> In SQL 2005, I have a XML with an one element with an empty value like
> so:
> <root>
> <test></test>
> </root>
> When I try and replace the value of (/root/test) it does not seem to
> work:
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @.xml
> returns this:
> <root>
> <test></test>
> </root>
>
> If however the element has a value already like so:
> <root>
> <test>some old value here already</test>
> </root>
> and I re-rerun the statement above:
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @.xml
> returns this:
> <root>
> <test>test new value</test>
> </root>
> Is something wrong there?
>
|||On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
<denis...@.online.microsoft.com> wrote:
> In the first case, when you use expression (/root/test/text())[1] you refer to
> something that doesn't exist (there is no text node under element test) and
> therefore nothing gets updated.
> What you need to do is insert a new text node inder element "test" like this
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('insert text{"test new value"} as first into (/root/test)[1] ')
> select @.xml
> I hope this helps.
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
> <doctorp...@.gmail.com> wrote in message
> news:1177013246.440921.259810@.d57g2000hsg.googlegr oups.com...
>
>
>
>
>
>
>
> - Show quoted text -
So I guess there is no one built-in statement that will work in both
scenarios? ie. One that will insert the text if one doesn't exist, and
one that will replace the text if it already exists. I guess I will
have to write a function?
|||You should probably use the XML datatype's exist() method to determin if there
is already a text node or not and then perform the proper action depending on
what case you're in.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegro ups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
> So I guess there is no one built-in statement that will work in both
> scenarios? ie. One that will insert the text if one doesn't exist, and
> one that will replace the text if it already exists. I guess I will
> have to write a function?
>
|||Try the following:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
set @.xml.modify('insert text{ "test
new value"} into (/root/test[not(text())])[1]')
select @.xml
Best regards
Michael
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegro ups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
> So I guess there is no one built-in statement that will work in both
> scenarios? ie. One that will insert the text if one doesn't exist, and
> one that will replace the text if it already exists. I guess I will
> have to write a function?
>
sql

replace value of ... variable

Hi
I have a variable @.IDENT. I'd like to change my data in XML and i use
replace value of (default value of .../DocumentRecId=0). I tried to do
in that way
UPDATE [Doc]
SET
doc_XML.modify('
replace value of (/DocumentDataSet/Document/DocumentRecId/text())[1]
with sql:variable("@.IDENT")')
FROM [dbo].[Document] AS [Doc]
WHERE doc_IDENT=@.IDENT
but I get errors.
"XQuery [dbo.Document.doc_XML.modify()]: The XQuery syntax
'/function()' is not supported."
Is there any posibilty to modify my data in XML using variables.
Greetings
RoanHello Roan,
R> I have a variable @.IDENT. I'd like to change my data in XML and i use
R> replace value of (default value of .../DocumentRecId=0). I tried to
R> do
R> in that way
R> UPDATE [Doc]
R> SET
R> doc_XML.modify('
R> replace value of
R> (/DocumentDataSet/Document/DocumentRecId/text())[1]
R> with sql:variable("@.IDENT")')
R> FROM [dbo].[Document] AS [Doc]
R> WHERE doc_IDENT=@.IDENT
This works for me:
drop table dbo.docs2
go
create table dbo.docs2(pkid tinyint identity(1,1),doc xml)
go
insert into dbo.docs2(doc) values ('<v>Some Value</v>')
go
declare @.v varchar(15)
set @.v= 'something else'
update dbo.docs2
set doc.modify('replace value of (/v/text())[1] with sql:variable("@.v")')
where pkid=1
go
select * from dbo.docs2
go
and so does set doc.modify('replace value of (/v/text())[1] with sql:column(
"pkid")').
With SP1. What version are you running (hint, what does @.@.version show?)
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thanks
I try tonight one more time and it wors...I think the problem was not
in replace value...
I have another question: this time i try to modify(add) value to node
like this
<DocumentNumberStyle />
I tryied to use replace value... but it doesn't work. Do you have any
idea have to do it.
Thanks for any help.
Roan|||Ok i solve the problem.
Greetings Roan

replace value of (sq:variable("@variable")) with ""

replace value of(/node/sql:variable("@.varname")/@.Status)[1] with
"pass"'

gives me The XQuery syntax '/function()' is not supported.

Is it not supported?

If it is not supported what is the best way to update a specific node out of many based on certain criteria?

like update Student Grade to A for Student node with name "John" and not any other, without having to use dynamic SQL.

Tim

|||

reproducing answer to this by Pohwan on another discussion alias. This is exactly what I wanted and it works if somebody else is in is same boat

*****************

Workaround,

/node/*[local-name()=sql:variable("@.varname")]/@.Status

instead of

/node/sql:variable("@.varname")/@.Status

--
Pohwan Han. Seoul.

**************

|||

Thanks, that worked wonders for me

replace value of (sq:variable("@variable")) with ""

replace value of(/node/sql:variable("@.varname")/@.Status)[1] with
"pass"'

gives me The XQuery syntax '/function()' is not supported.

Is it not supported?

If it is not supported what is the best way to update a specific node out of many based on certain criteria?

like update Student Grade to A for Student node with name "John" and not any other, without having to use dynamic SQL.

Tim

|||

reproducing answer to this by Pohwan on another discussion alias. This is exactly what I wanted and it works if somebody else is in is same boat

*****************

Workaround,

/node/*[local-name()=sql:variable("@.varname")]/@.Status

instead of

/node/sql:variable("@.varname")/@.Status

--
Pohwan Han. Seoul.

**************

|||

Thanks, that worked wonders for me

replace value of

Can someone please enlighten me on a specific way "replace value of" is used?

I'd simply like to know if replace value of can be used to udpate an empty node value. I can't get it to do that, so I'm thinking it can't, but I would certainly like to be corrected if it is possible.

For example, suppose of I have the following in a Typed XML column:

Code Snippet

<Root>

<FirstName></FirstName>

</Root>

When I use replace value of on the above, it does not update it. However, if i have the following:

Code Snippet

<Root>

<FirstName>Scott</FirstName>

<Root>

replace value of will replace the value with whatever I specify.

If this is the way it is supposed to behave, how do I update a NULL value with an actual value?

Thanks in advance...

use the following expression,

Code Snippet

Declare @.XML as XML

Set @.XML='<Root>

<FirstName>Mani</FirstName>

<FirstName>Scott</FirstName>

<FirstName></FirstName>

<FirstName/>

</Root>'

Select

Data.Nodes.query('

<FirstName>

{

if( string(.) = "" )

then

"No Name"

else

string(.)

}

</FirstName>')

From

@.XML.nodes('/Root/FirstName') as Data(Nodes)

|||

The following example works fine for me (actually I create the schema collection first, then run the other statements):

Code Snippet

CREATEXMLSCHEMACOLLECTION dbo.schema2 AS'

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Root">

<xs:complexType>

<xs:sequence>

<xs:element name="FirstName" type="xs:string"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

';

DECLARE @.example xml(dbo.schema2);

SET @.example ='<Root><FirstName></FirstName></Root>';

SELECT @.example;

SET @.example.modify('

replace value of (Root/FirstName)[1]

with "Scott"

');

SELECT @.example;

If I try the same replace statement with an untyped xml variable then it does not work, the error says that the element needs to be typed.

|||

Since you have schema defined as element FirstName onlu occur once under Root, you can get rid the ()[1] part.

Code Snippet

DECLARE @.example xml(document dbo.schema2);

SET @.example ='<Root><FirstName></FirstName></Root>';

SELECT @.example;

SET @.example.modify('

replace value of Root/FirstName

with "Scott"

');

SELECT @.example;

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

Replace null value

I have NULL value of data field, and I want to replace with '-' character in the crystal report. I try to write this formula "IIF(IsNull(table.field),"-",(table.field))", but the result still Null(blank). Can any body help me?
Thank'sOne option is to create a formula, and then place the formula in the report where the current field is located.

The formula would be:

if isnull(table.field) then "-"
else table.field|||Hi abstract, thanks for ur reply. Now i can fix the problem|||I don't think you understood what I was trying to explain. You need to create a Formula Field; NOT create a formula in the suppress box.

I have used this before, so I know it works.

abstract|||Never mind my last post, I misread something.

abstract|||I think this will work if it is in formula

IIF(IsNull(table.field),"-",(table.field))