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

Change Management for Jobs and DTS packages

We have a solution for change management for our databases, but how do others
handle updates/modifications to jobs or DTS packages. Does anyone know of a
tool to help us do this or is this one of those things we might have to grow
our own solution by checking out the tables in msdb?
Any suggestions would be appreciated.
Thanks,
Linda
When saving DTS packages on the SQL Server or as structured storage files
(*.dts), previous versions of the package are automatically saved. You can
go back to any previous version you wish to.
As for change management, I would suggest setting a user password so the
package can be executed by users, but not viewed or modified. Then the
package can only be altered with an owner password.
But if you want more details on changes, save your DTS packages as
structured storage files and add them to VSS (or whatever versioning
software you're using). Then you are able to capture check out and check in
details as well, and can reference change request id's etc.
Simon Worth
"lslmustang" <lslmustang@.discussions.microsoft.com> wrote in message
news:6F69B8E7-DAFA-4755-AECE-952B7A5F8142@.microsoft.com...
> We have a solution for change management for our databases, but how do
others
> handle updates/modifications to jobs or DTS packages. Does anyone know of
a
> tool to help us do this or is this one of those things we might have to
grow
> our own solution by checking out the tables in msdb?
> Any suggestions would be appreciated.
> Thanks,
> Linda
|||That's an idea we may use. What about Jobs though?
"Simon Worth" wrote:

> When saving DTS packages on the SQL Server or as structured storage files
> (*.dts), previous versions of the package are automatically saved. You can
> go back to any previous version you wish to.
> As for change management, I would suggest setting a user password so the
> package can be executed by users, but not viewed or modified. Then the
> package can only be altered with an owner password.
> But if you want more details on changes, save your DTS packages as
> structured storage files and add them to VSS (or whatever versioning
> software you're using). Then you are able to capture check out and check in
> details as well, and can reference change request id's etc.
> --
> Simon Worth
>
> "lslmustang" <lslmustang@.discussions.microsoft.com> wrote in message
> news:6F69B8E7-DAFA-4755-AECE-952B7A5F8142@.microsoft.com...
> others
> a
> grow
>
>
|||Simply script the job and keep the source script under source control
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"lslmustang" <lslmustang@.discussions.microsoft.com> wrote in message
news:1ABF1581-CF71-48A7-87AD-D047AD1BF4A1@.microsoft.com...[vbcol=seagreen]
> That's an idea we may use. What about Jobs though?
> "Simon Worth" wrote: