quote:
Originally posted by mikecoleman407
Existing data looks like this:
Fname - JANE
Lname - DOE
In that case its a LOT simpler, i.e. all names are single-part one word.
Here's a test rig into which you can put names that might be more complex - if you don't have any then this code will work as-is.
CREATE TABLE #TEMP
(
ID int IDENTITY(1,1),
Fname varchar(20),
Lname varchar(20),
IsFixed bit
)
INSERT INTO #TEMP
(
Fname, Lname
)
SELECT 'JANE', 'DOE' UNION ALL
SELECT 'JOHN', 'DOE' UNION ALL
SELECT 'COMPLEX NAME', 'XXX' UNION ALL
SELECT 'HYPHEN-NAME', 'XXX'
-- ... add any additional example tests here ...
-- Before
SELECT *
FROM #TEMP
UPDATE U
SET Fname = UPPER(LEFT(Fname, 1))
+ LOWER(SUBSTRING(Fname, 2, LEN(Fname)-1)),
Lname = UPPER(LEFT(Lname, 1))
+ LOWER(SUBSTRING(Lname, 2, LEN(Lname)-1)),
IsFixed = 1 -- Indicate that the row WAS processed
FROM #TEMP AS U
WHERE Fname NOT LIKE '%[^A-Za-z]%'
AND Lname NOT LIKE '%[^A-Za-z]%'
-- After
SELECT *
FROM #TEMP
ORDER BY CASE WHEN IsFixed = 1 THEN 2 ELSE 1 END, -- Sort exceptions first
ID
GO
DROP TABLE #TEMP
GO
Convert All CAPS Data
Posted - 2015-04-15 : 16:41:05
then you can just use whichever algortihm works best for you, doing a Google for "Proper Case" or "Title Case" will give you some starters.