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 a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.
You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.