Help with clean up query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with clean up query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-04-10 : 11:42:06

I have the following query which strips out middle initial data from the first_name column and populate the middle_initial column with the relevant data.

What my query does not handle is when a first name has a single character (IE: A). In its current state, the "A" would be moved to the middle_initial field with a period added to the end (IE A.). The first_name column would also include "A". Basically, when a single character first name is found in the first_name column, I do not want to populate the middle_name field.

Hope this does not sound too cryptic; query is below along with some sample data when run.

SELECT first_name,
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name))),1) +'.'
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=2 THEN LEN(LTRIM(RTRIM(first_name)))-2 ELSE
LEN(LTRIM(RTRIM(first_name))) END)
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM contact

Sample Data
first_name, middle_initial, first_name_removing_initials,
Paul, NULL, Paul
A, A., A,
A Fred, NULL, A Fred,
Aaron, NULL, Aaron

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 12:16:58

Add a WHERE condition: