help with substring, etc.... - 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 substring, etc....

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-21 : 10:49:02

I have a column called first_name which has name values including a middle initial (Tim E, Dave F, etc...). I would like to strip out the characters after a space and containing a single character. from there, I would like to take the stripped values and insert them into a middle_initial field, trimming the preceeding space.
The final result would be a clean first_name column, and a new column called middle_initial as shown:

first_name middle_initial
Tim E
Dave F

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 10:55:33

SELECT LEFT(first_name,CHARINDEX(' ',first_name)-1) AS 'first_name',
RIGHT(first_name,CHARINDEX(' ',first_name)+1) AS 'middle_initial'
FROM Table

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-23 : 10:42:24

That just doesn't work even for just a stand alone first name never mind a first name with a space in it. Here's the proof...

DECLARE @yourtable TABLE (First_Name VARCHAR(20),FNameShouldBe VARCHAR(20), MiShouldBe CHAR(1))
 INSERT INTO @yourtable (First_Name,FNameShouldBe,MiShouldBe)
-- SELECT 'Anne','Anne',NULL             UNION ALL --Had to comment out because blew up!!!
 SELECT 'Anne K','Anne','K'            UNION ALL
 SELECT 'Anne Marie','Anne Marie',NULL UNION ALL
 SELECT 'Anne Marie K','Anne Marie','K'

 SELECT First_Name AS OriginalFirstName,
        LEFT(First_Name,CHARINDEX(' ',First_Name)-1) AS 'First_Name',
        RIGHT(First_Name,CHARINDEX(' ',First_Name)+1) AS 'Middle_Initial',
        FNameShouldBe,
        MiShouldBe
   FROM @yourtable

Not including the fact that there could be periods, multiple adjacent spaces, and a wealth of other naming problems, the following will do the trick...

 SELECT 
        FirstName = CASE 
                        WHEN RIGHT(First_Name,2) LIKE ' [A-Z]'
                        THEN LEFT(First_Name,LEN(First_Name)-2)
                        ELSE First_Name
                    END,
        MI        = CASE 
                        WHEN RIGHT(First_Name,2) LIKE ' [A-Z]'
                        THEN RIGHT(First_Name,1)
                        ELSE NULL
                    END
   FROM @yourtable

Of course, you will need to replace "@yourtable" with the real table name.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources