How do I correct capitalization of hyphenated names in sql?
I tried to get all the names in a very large database into proper format by using
How do I find the first letter after the dash and capitalize it without changing anything else?
UPDATE Candidates
SET Last_Name=UPPER(LEFT(Last_Name,1))+LOWER(SUBSTRING(Last_Name,2,LEN(Last_Name)))
How do I find the first letter after the dash and capitalize it without changing anything else?
Last Comment
ASKER
Oh no :-( I didn't think of those.
But for the dashes...something like
UPDATE Candidates
SET Last_Name= Name1 + '-' + UPPER(LEFT(Name2,1)) + SUBSTRING(Name2 , 2, LEN(Name2)
WHERE (Name1 IN (SELECT SUBSTRING (Last_Name,0,CHARINDEX('-',Last_Name,)) AS Name1
AND Name2 IN (SUBSTRING(Last_Name,CHARINDEX('-',Last_Name )+1,LEN(Last_Name)) AS Name2)
🤷♀️ ?
But for the dashes...something like
UPDATE Candidates
SET Last_Name= Name1 + '-' + UPPER(LEFT(Name2,1)) + SUBSTRING(Name2 , 2, LEN(Name2)
WHERE (Name1 IN (SELECT SUBSTRING (Last_Name,0,CHARINDEX('-',Last_Name,)) AS Name1
AND Name2 IN (SUBSTRING(Last_Name,CHARINDEX('-',Last_Name )+1,LEN(Last_Name)) AS Name2)
🤷♀️ ?
I don't think you should be focusing on doing it in the UPDATE statement like thatbut rather, creating a function that can be referenced in your update statement.
Reference
https://lanitdev.wordpress.com/2010/05/28/converting-names-to-mixed-casecamel-case-in-sql-server/
(The above function MAY need some editing for you)
Then you would have a SQL statement like (assuming your function was named the same as in the link):
UPDATE Candidates SET Last_Name=GetCamelCaseName(Last_Name)
(Technically, you don't need a "WHERE" clause since this only affects the fields that need it).
Reference
https://lanitdev.wordpress.com/2010/05/28/converting-names-to-mixed-casecamel-case-in-sql-server/
(The above function MAY need some editing for you)
Then you would have a SQL statement like (assuming your function was named the same as in the link):
UPDATE Candidates SET Last_Name=GetCamelCaseName(Last_Name)
(Technically, you don't need a "WHERE" clause since this only affects the fields that need it).
ASKER
Wow, that's quite the code there. Unfortunately, I don't have access to the SQL server and my permissions are limited in a small "sql workbench". I know I can select, update, delete, etc. But I get a permission denied error if I try to create anything, including functions.
That you for the link to that code. I'm sure I'll make use of it on other databases where I have more access.
That you for the link to that code. I'm sure I'll make use of it on other databases where I have more access.
ASKER
I got a count of the records with name dashes and it's just shy of 21K. A number that Excel can easily handle. So, I'm going to export the Name and ID, fix it in Excel, then Update the db using the ID to match them back up.
Thank you for your help Lee. That code is going into my "keeper" folder.
Thank you for your help Lee. That code is going into my "keeper" folder.
Outside of SQL, if you can do it in Excel, you can create a similar Macro / Custom function and then fix it there.
Technically, you could do it with a VBScript but you need to connect to the database.
High level view:
1. Connect to your database
2. Query your database and put the results in a recordset.
3. Create a custom VB function similar to the SQL function - which would likely be just a bunch of if statements similar to the SQL function)
4. Loop through the recordset updating the names by wrapping them in your vbfunction.
Technically, you could do it with a VBScript but you need to connect to the database.
High level view:
1. Connect to your database
2. Query your database and put the results in a recordset.
3. Create a custom VB function similar to the SQL function - which would likely be just a bunch of if statements similar to the SQL function)
4. Loop through the recordset updating the names by wrapping them in your vbfunction.
Set DBRS = Conn.Execute("SELECT UniqueIDField, Last_Name FROM yourtable")
Do Until DBRS.EOF
SQL = "UPDATE yourtable SET Last_Name = '" & VBCamelCaseFunction(DBRS("LastName")) & "' WHERE UniqueIDField = " & DBRS("UniqueIDField")
DBRS.MoveNext
Loop
ASKER
I can't connect to the database via Excel. I can only export records from the db into csv. I can easily correct the names in Excel. Actually already did that. But it looks like updating the records in the db is still going to be a lot of manual entry. I didn't know the CASE statement was limited to 255 arguments. I just read that.
I have 20K+ records to update.
I have 20K+ records to update.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Scott,
Thank you! I knew there had to be a way, but I couldn't come up with the code.
I still get the error "SQL statement does not have permission to use specified resource." Do you think it's the loop causing it? I'll try running it without the loop if you don't think it'll harm anything.
eta: tomorrow, since it's way past the end of my workday
Thank you! I knew there had to be a way, but I couldn't come up with the code.
I still get the error "SQL statement does not have permission to use specified resource." Do you think it's the loop causing it? I'll try running it without the loop if you don't think it'll harm anything.
eta: tomorrow, since it's way past the end of my workday
Yeah, I guess it could be. I guess try it without the loop -- if it works, you can just run it yourself again until you get a non-zero row count.
Not a very specific error msg though, obviously, lol. I don't know of any restrictions on using the COLLATE clause. You might try a quick SELECT with that and see if you get an error.
Since you issued an UPDATE earlier, I don't see how UPDATE could be the problem.
Not a very specific error msg though, obviously, lol. I don't know of any restrictions on using the COLLATE clause. You might try a quick SELECT with that and see if you get an error.
Since you issued an UPDATE earlier, I don't see how UPDATE could be the problem.
I guess make sure too that the needed settings are in effect in case an index has been defined on a computed column? (yeah, very obscure, but, again, no details from the error msg itself):
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF);
SET QUOTED_IDENTIFIER ON;
UPDATE ...same_as_before...
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF);
SET QUOTED_IDENTIFIER ON;
UPDATE ...same_as_before...
ASKER
The loop was the problem. COLLATE works fine. I'm working through a SaaS platform that allows me access to my data but nothing else. It seems that I'm blocked from all server resources. They offer an API, but since I've never used one before I don't want to spend money on an unfamiliar resource.
I can't thank you enough for the code. You saved me countless hours of work.
I've worked with SQL for decades but there is still so much I don't know. This was my first introduction to STUFF, PATINDEX and COLLATE.
Thanks again.
I can't thank you enough for the code. You saved me countless hours of work.
I've worked with SQL for decades but there is still so much I don't know. This was my first introduction to STUFF, PATINDEX and COLLATE.
Thanks again.
Yeah, STUFF is a pretty slick function.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Of course, you still have a problem with people with names like deGrom or McDonalds or MacDouglas. My point is, you probably just want to accept that certain names will be problematic. Odds are, those people have probably already accepted that.