T-SQL code to extract variable length characters from a string

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 2000 Forums

Transact-SQL (2000)

T-SQL code to extract variable length characters from a string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-11 : 08:35:45

rick writes "I have a database of 5000 names in the string format 'lastname,firstname(sp)midinitial.' (there is a space between the firstname and midinitial) that I need to pull apart into separate appropriate fields in another table. This is real easy to do using ADO code, but I can't get a handle on it using just T-SQL code. The issues come from assigning temp variables, finding the length for the lastname and first name, and running this on each record as a type of batch. I'm using SQL SVR 2k w/ all service packs."

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-11 : 13:40:43

Are you positive that all the rows have the exact same format?

The following query might help you identify those that don't.

SELECT Name
FROM YourTable
WHERE PATINDEX('%,% _', Name) = 0

Assuming that query returns no rows, it's simply a matter of:

SELECT
LEFT(Name, CHARINDEX(',', Name) - 1) AS LastName,
SUBSTRING(Name, CHARINDEX(',', Name) + 1, CHARINDEX(' ', Name) - CHARINDEX(',', Name)) AS FirstName,
RIGHT(Name, 1) AS MiddleInitial
FROM YourTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources