Changing Character Case

Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.Hello,

that depends on the database that you use:

for Oracle and MySQL and MSSQL : UPDATE table SET company = UPPER(company);

If you use another database, please let us know ...

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by acg_ray
Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.
In Oracle, use the INITCAP function. However, this will convert "LTD" to "Ltd" in your example above.|||UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.|||Originally posted by acg_ray
UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.
I just took a look at some SQL Server documentation, and see that there seems to be no equivalent of INITCAP. I guess you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You would use CHARINDEX to find the spaces, then UPPER the character following each space.|||Thanks. That's where I figured this was heading. That's a good direction, and I should be able to proceed from here. I appreciate the info.|||I was able to get this to work (for the most part) with a combination of LEFT, RIGHT, SUBSTRING, LEN, CHARINDEX, UPPER, and LOWER commands. So it's not a bad tool for me to have on hand.

I discovered as an afternote, that if I exported my results to Excel, I could use a command Proper(field) that would automatically do this for me. I could have then brought the results back into MSSQL. Really, a much easier process, since my data sets are small enough to be handled by Excel (15,000 rows).sql

Changing Attribute Label Name in a Shared Dimension

Hi,

I have a dimension called Branch that is linked to alot of Branch-related fields in my fact table like Send-To Branch, Collection Branch, Receiving Branch etc.

Since my Branch dimension is shared, if i change the description name of my attribute in Send-To Branch Dimension Structure, it will affect the rest of my Dimensions related to Branch. Can it be possible that i can change the attribute name in each branch dimension without affecting the others?

example:

Send-To Branch

Send-To Branch ID

Send-ToBranch

Send-To Branch Type

Collection Branch

Collection Branch ID

Collection Branch

Collection Branch Type

cherriesh

No, It is not possible to rename attributes in each instance of a role playing dimension.|||

So that means, I need to create SQL views to instantiate my Branch Dimension and link them individually to the different branch-related foreign keys in my fact table?

is it too costly in terms of the storage?

cherriesh

|||

Well it either means that you need to come up with a more generic naming convention for your attributes. So that your cube dimensions would look something like the following...

Send-To Branch

Branch ID

Branch

Branch Type

Collection Branch

Branch ID

Branch

Branch Type

Although this is not without issues as it can be confusing in tools like Excel where you cannot always tell which dimension an attribute came from.

I don't know if you need to create SQL views, you might be able to get away with creating multiple identical dimensions off the same dimension table(s). I'm not sure as I usually use the generic attribute name approach.

It is not usually too costly in terms of disk storage as most dimensions are pretty small when compared to the fact tables. It can have an impact when you consider cache storage as SSAS will be caching multiple sets of identical dimension keys which it does not need to do with role playing dimensions. You will also have to consider the extra processing time and the maintenance overhead of having to keep multiple dimensions synchronized.

Changing a record after comparing one database table to another

I'm using SQL Server 2005 Express.

In my main database table I have many fields but the two following fields are my main concern.

1) email_address

2) unsubscribe

In my secondary database table I have one record only.

1) email_address

What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)

I'd really appreciate any help I can get.

Thanks,

Bill

Something like this should work for you.

Code Snippet

UPDATE MyMainTable
SET UnSubscribe = {Put Your Value Here}

FROM MyMainTable m
JOIN MySecondaryTable s
ON m.eMail_Address = s.eMail_Address

If there is a match, [UnSubscribe] is changed, if there is NO match, nothing happens. This will work with multiple rows in the secondary table.

|||

I'll try this tonight. Thanks a lot Arnie.

Bill

Changing a fields backgroung color IIF

Hi All,
I need to change the backgroung color to a field in the background property...

=iif(Fields!act_shp_dt.Value <> Fields!prm_shp_dt.Value , " Late ","")
This is the field and I want it to change color if the statement is true. Can anyone help?
Thank you in advanced, Kerrie

I never attempted this on a per cell basis but it should work;
Create a function in the code section, something like this...
function getColor(s1 as string, s2 as string) as String
getColor = iif((s1 <> s2), "Red", "White")
end function
then call the function from your textbox like this...
<BackgroundColor>=Code.getColor(Fields!act_shp_dt.Value , Fields!prm_shp_dt.Value )</BackgroundColor>
|||you code seems fine but make sure you use the colors you want to show:
=iif(Fields!act_shp_dt.Value <> Fields!prm_shp_dt.Value , " <some_color>","<some_color>")

Changing a field value to NULL

I know the trick of using ctrl-0 to change it individually, but is there a
syntax that I can use as in the UPDATE to change a BUNCH of fields at the
same time. When changing fields I usually use the
UPDATE <table>
SET <field> = <parameter>
WHERE <field> = <parameter>
How do I do this to set a field as null where the parameter is 'No
Response'?
TIA
JCNever mind. I figured out what I was doing wrong. Dumb mistake.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:4F1F5AC7-E93B-4E0D-BA69-13AE90A90F71@.microsoft.com...
>I know the trick of using ctrl-0 to change it individually, but is there a
>syntax that I can use as in the UPDATE to change a BUNCH of fields at the
>same time. When changing fields I usually use the
> UPDATE <table>
> SET <field> = <parameter>
> WHERE <field> = <parameter>
> How do I do this to set a field as null where the parameter is 'No
> Response'?
> TIA
> JC

Changed fields

In our applications we have a table named ChgMstr (Change
Log) that logs changes made to each record. It includes
the Field Name, Date & Time, the user making the change,
and the Before and After values of the field.
I am trying to setup an Update trigger that will loop
through the Deleted and Inserted fields, find which fields
have changed, and then either INSERT directly into the
ChgMstr table or call a Stored Procedure to do it.
However, I cannot find a way (even with Dynamic SQL) to
build a SELECT string that can be EXECuted. When I do, I
get "Invalid Object" messages for both Deleted and
Inserted. It appears that once the EXEC statement is sent
that Deleted and Inserted go out of scope.
Anyone got any ideas. I can provide the contents of the
trigger, if needed.Bryan,
The inserted and deleted tables will not be available to a stored
procedure. You could copy their contents into a temporary table for use by
the stored procedure, but it seems to me unlikely that you need a stored
procedure just to insert into your ChgMstr table. Can you do something like
this:
insert into ChgMster
select N'ColumnA', getdate(), suser_sname(), d.ColumnA, i.ColumnA
from inserted i, deleted d
where i.primaryKey = d.primaryKey
union all
select N'ColumnB', getdate(), suser_sname(), d.ColumnB, i.ColumnB
from inserted i, deleted d
where i.primaryKey = d.primaryKey
This assumes that the primary key column is not modified. If it is, unless
you have another unique constraint on columns that don't change, the
question of what the original and final values of that column are is not
discernable from the inserted and deleted tables in some multi-row updates,
such as
update T set
pk = case when 1 then 11 when 4 then 12 when 8 then 13 end
from T
where pk in (1, 4, 8)
Then the inserted table will contain rows with pk 11, 12, and 13, and the
deleted table will contain rows with pk 1, 4, and 8, but there is no way to
say which of 1, 4, and 8 because which of 11, 12, 13 by looking at the
inserted and deleted tables.
SK
"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in message
news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
> In our applications we have a table named ChgMstr (Change
> Log) that logs changes made to each record. It includes
> the Field Name, Date & Time, the user making the change,
> and the Before and After values of the field.
> I am trying to setup an Update trigger that will loop
> through the Deleted and Inserted fields, find which fields
> have changed, and then either INSERT directly into the
> ChgMstr table or call a Stored Procedure to do it.
> However, I cannot find a way (even with Dynamic SQL) to
> build a SELECT string that can be EXECuted. When I do, I
> get "Invalid Object" messages for both Deleted and
> Inserted. It appears that once the EXEC statement is sent
> that Deleted and Inserted go out of scope.
> Anyone got any ideas. I can provide the contents of the
> trigger, if needed.|||1. Forgive my ignorance, but what does the 'N' in select
N'Column A' do?
2. Will I have to do a union for each field in the table?
I'm looking for a generic way to do this so the trigger
does not need to be changed each time fields are added or
deleted.
Thanks,
Bryan

>--Original Message--
>Bryan,
> The inserted and deleted tables will not be available
to a stored
>procedure. You could copy their contents into a
temporary table for use by
>the stored procedure, but it seems to me unlikely that
you need a stored
>procedure just to insert into your ChgMstr table. Can
you do something like
>this:
>insert into ChgMster
>select N'ColumnA', getdate(), suser_sname(), d.ColumnA,
i.ColumnA
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>union all
>select N'ColumnB', getdate(), suser_sname(), d.ColumnB,
i.ColumnB
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>This assumes that the primary key column is not
modified. If it is, unless
>you have another unique constraint on columns that don't
change, the
>question of what the original and final values of that
column are is not
>discernable from the inserted and deleted tables in some
multi-row updates,
>such as
>update T set
> pk = case when 1 then 11 when 4 then 12 when 8 then 13
end
>from T
>where pk in (1, 4, 8)
>Then the inserted table will contain rows with pk 11, 12,
and 13, and the
>deleted table will contain rows with pk 1, 4, and 8, but
there is no way to
>say which of 1, 4, and 8 because which of 11, 12, 13 by
looking at the
>inserted and deleted tables.
>SK
>"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in
message
>news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
(Change
fields
I
sent
>
>.
>|||Bryan,
The N just signifies that the string is Unicode. Object names in SQL
Server are in Unicode, but it won't hurt to leave this out if the name
only uses ASCII.
I generally don't think it's a great idea to write generic code that
doesn't "know" the table structure. You could generate the code for
triggers like this automatically with something like this:
select 'insert into ChgMster' oneLine, 0 ORDINAL_POSITION
union all
select 'select
'+q_column_name+',
'+' getdate(),
suser_sname(),
d.'+q_column_name+',
'+' i.'+q_column_name+'
from inserted i, deleted d
where i.primaryKey = d.primaryKey
and i.'+q_column_name+'<>d.'+q_column_name+
case when ORDINAL_POSITION = (
select max(ORDINAL_POSITION)
from Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) then '' else '
union all'
end, ORDINAL_POSITION
from (
select quotename(COLUMN_NAME) as q_column_name, ORDINAL_POSITION
FROM Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) C
order by ORDINAL_POSITION
There are also third-party products to do this kind of logging.
SK
Bryan A. Jackson wrote:
>1. Forgive my ignorance, but what does the 'N' in select
>N'Column A' do?
>2. Will I have to do a union for each field in the table?
>I'm looking for a generic way to do this so the trigger
>does not need to be changed each time fields are added or
>deleted.
>Thanks,
>Bryan
>
>
>to a stored
>
>temporary table for use by
>
>you need a stored
>
>you do something like
>
>i.ColumnA
>
>i.ColumnB
>
>modified. If it is, unless
>
>change, the
>
>column are is not
>
>multi-row updates,
>
>end
>
>and 13, and the
>
>there is no way to
>
>looking at the
>
>message
>
>(Change
>
>fields
>
>I
>
>sent
>

Change value displayed in last row in table?

I have a table which displays years (2007, 2006, 2005, etc.) as one of its fields. I need to have it display Year + " and prior" for the last row. Any thoughts on how I might do this? Thanks!

Hello,

Try this in your expression:

=Iif(CountRows("DataSetName") = RunningValue(1=1, Count, "DataSetName"), cStr(Fields!Year.Value) & " and prior", cStr(Fields!Year.Value))

Hope this helps.

Jarret

|||Great, that did it!

Change ntext to nvarchar(max) in a live database

I have a live SQL 2005 database that has ntext fields, when the ntext fields go over 4000 chars the record can no longer be edited. It throws a string or binary data would be truncated error. I tried turning text in row OFF, but it did not work. Can anyone forsee any problems with changing the ntext fields to nvarchar(max) in the live database? Also, I came across sp_tableoption N'MyTable', 'large value types out of row', 'ON', does this work for ntext also? sp_tableoption N'MyTable', 'text in row', 'OFF' did not do anything.

Any help would be appreciated.

Have you set TEXTSIZE option for the connection? Check this using:

DBCC USEROPTIONS

|||

Where and how do I do this? Also I changed the ntext fields in the database to NVarChar(MAX), so now I can modify the values in the database directly, but when I do it through the application and the VarChar(MAX) fields are over 4000 it does not save the changes, but does not return an error either. It returns as if the stored procedure executed successfully.

|||

I'm not sure why you felt the need to create yet another thread, but the answer remains the same (and the 3rd time I'm giving it to you):

The problem is more likely that the parameters are being declared as either the wrong type, or you are not declaring the type at all, and letting it default. Make sure your ntext parameters are declared as such.

|||

Actually it was a problem upgrading a SQL 2000 database to SQL 2005 then modifying it, I had to recreate the table. Once I recreated the table and did a Select Into, the problem was resolved.

And I created another thread because I wanted to change the datatypes from ntext to nvarchar(max) in a live database and wanted to know if it was going to have any ill effects. In my experience, when you ask a different question in an existing thread, you do not get an answer for both questions, so I wanted to keep them separated.