Change formatting of characters in SQL 2008 db from all upper-case only first letter capitalized.

Hello,

I need to preface this question by stating that I know what the acronym SQL stands for, but that's about the extent of my SQL knowledge at this time (I'm trying though!).

I am the administrator for a Microsoft Axapta 2012 ERP system, which uses SQL 2008 for its databases.

It appears that for the past few years, there has been inconsistencies with how our engineers have formatted the names of the parts that we manufacture.  Many use all capital letters, while some use first letter capital only, and one 'lone wolf' used all lower case.

The CEO of our company noticed this and wants all 14,000+ parts changed to first letter capitalized, remaining letters lower case.  In some instances, the field where the part number is listed begins with a special character (hyphen or asterisk most of the time) or a number... in these instances, those characters would have to be ignored, and the first alpha character would need to be capitalized.

Is this as complicated as it sounds?  If it is, is there anyone out there who might be able to provide some assistance?

Thank you in advance.

Scott
Scott MilnerApplication AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
- Compile a list of fields along with there table names.
(In your case I guess it will be Parts.PartName for TableName.PartName)

- Verify Part Names do include blank space and if even some do you want just to make all lower case except the first letter.

- Then run this query:

Update  Parts Set PartName = functionToUpdate(PartName);

functionToUpdate() to be supplied after you respond to the above assumptions.

Mike
0
Scott PletcherSenior DBACommented:
>> Is this as complicated as it sounds? <<

Nope, not for just the first letter.  I'm assuming your db is NOT case sensitive -- if it is, the code will have to be adjusted slightly:


UPDATE dbo.parts_table
SET name = STUFF(LOWER(name), PATINDEX('%[a-z]%', name), 1,
    UPPER(SUBSTRING(name, PATINDEX('%[a-z]%', name), 1)))
WHERE name LIKE '%[a-z]%'
0
Mike EghtebasDatabase and Application DeveloperCommented:
Based on the above assumptions, I have:

Update  Parts Set PartName = Upper(Substring(PartName ,1,1)) + Lower(Substring(PartName ,2, 100));

Open in new window


If you suspect there are empty spaces on the left, then use:
Update  Parts Set PartName = Upper(Substring(LTrim(PartName) ,1,1)) + Lower(Substring(LTrim(PartName) ,2, 100));

Open in new window


Mike
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott MilnerApplication AdministratorAuthor Commented:
Hi Mike,

Part names do include blank spaces within the string, but never at the front.  

For example, in the table InventTable, we have a part number whose PRODUCTNAME field reads

~~GREASE RETAINER 1-17/32"ID X 3"OD

If I could, I would change the PRODUCTNAME to read

~~Grease retainer 1-17/32"id X 3"od

Scott
0
Scott MilnerApplication AdministratorAuthor Commented:
Thanks ScottPletcher.

I wasn't entirely clear in my problem description, and tried to clarify in my response to Mike.

Does what you are recommending still apply?

To both Scott and Mike... your responses are very similar... are they just two ways of approaching the same problem?

thanks very much for your help, and your patience with me.

Scott
0
Scott PletcherSenior DBACommented:
My code will handle any leading char(s) before the first alpha, not just spaces, as that seemed to be a requirement from your initial q:

In some instances, the field where the part number is listed begins with a special character (hyphen or asterisk most of the time)

For example:

SELECT name,
     STUFF(LOWER(name), PATINDEX('%[a-z]%', name), 1,
     UPPER(SUBSTRING(name, PATINDEX('%[a-z]%', name), 1))) as revised_name
FROM (
    SELECT '**PART1' AS name UNION ALL
    SELECT '11part' UNION ALL
    SELECT '--5687y0avop0-'
    ) AS test_names
WHERE name LIKE '%[a-z]%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott MilnerApplication AdministratorAuthor Commented:
thank you to you both...

I'll test the suggestions on my test server and get back to you tomorrow.

I appreciate the help!

Scott
0
Mike EghtebasDatabase and Application DeveloperCommented:
smilner71,

I myself will choose the solution from Scott. He gave you a better solution and also gave me good info to learn from.

Mike
0
Scott MilnerApplication AdministratorAuthor Commented:
I'm sorry to both of you for not getting back to you.  Another 'fire' grabbed my attention, and I forgot to come back to the question.

Scott, your suggestion worked great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.