0

I am stuck trying to capitalise the first character of the first name and surname. I have formatted the names a lot already but i am stuck as the how to change the first letter of the surname when the length for names is always different.

CREATE TABLE:

CREATE TABLE Table1 (
Name varchar(30)
)

INSERT INTO Table1 (Name)
VALUES ('Domain/fred.smith'),
('Fred Smith@hotmail.com'),
('DIRECTOR/David lee'),
('DIRECTOR/andrew opal');

CURRENT QUERY:

SELECT 
REPLACE([Name], '.', ' '),
UPPER(LEFT([Name],1))+LOWER(SUBSTRING([Name],2,LEN([Name])))
FROM
(
SELECT
CASE WHEN Name LIKE '%@%' THEN SUBSTRING(Name ,0,CHARINDEX('@',[Name])) 
WHEN Name LIKE '%DIRECTOR%' THEN RIGHT([Name],(Len([Name])-9)) 
ELSE RIGHT([Name],(Len([Name])-7)) END AS [Name]
FROM Table1
) S

CURRENT RESULT:

|             |             |
|-------------|-------------|
|  fred smith |  Fred.smith |
|  Fred Smith |  Fred smith |
|   David lee |   David lee |
| andrew opal | Andrew opal |

But i am looking for:

|             |
|-------------|
|  Fred Smith |
|  Fred Smith |
|   David See |
| Andrew Opal |

Can anyone help me further? Thanks

SQL Fiddle - http://sqlfiddle.com/#!18/eb0f4/1

Ryan Gadsdon
  • 2,272
  • 4
  • 31
  • 56
  • 1
    This is not appropriate for a SQL query. If you really need this functionality, write a user-defined function. – Gordon Linoff Mar 14 '18 at 12:16
  • 1
    Looks like this was answered in this URL, which requires a function as Gordon Linoff suggests: https://stackoverflow.com/questions/15290754/sql-capitalize-first-letter-only – DarkMark Mar 14 '18 at 12:52
  • Saving different categories of data within the same string field is a violation of First Normal Form, because the data is not atomic. Also, if someone gets married and changes their last name then how are you going to do that? – Zorkolot Mar 14 '18 at 13:11

0 Answers0