• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

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
0
Scott Milner
Asked:
Scott Milner
  • 4
  • 3
  • 2
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now