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

SQL Server Development (2000)

substring

Author

Topic

hongho2
Starting Member

6 Posts

Posted - 2005-10-25 : 23:30:59

I have the data from table look like this

Bryan W Lee1
Jim Brook1

I want it to return with is format

FNAME LNAME MI
===== ===== ===
Bryan Lee W
Jim Brook

Here is my syntax but it not quite wokring correctly

select name
from acct

SELECT
LName =
CASE
WHEN PARSENAME(REPLACE(name, ' ', '.'), 3) IS NULL THEN ''
ELSE PARSENAME(REPLACE(name, ' ', '.'), 1)
END,

FName =
CASE
WHEN PARSENAME(REPLACE(name, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(name, ' ', '.'), 2)
ELSE PARSENAME(REPLACE(name, ' ', '.'), 3)
END,

MName =
CASE
WHEN PARSENAME(REPLACE(name, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(name, ' ', '.'), 1)
ELSE PARSENAME(REPLACE(name, ' ', '.'), 2)
END
FROM acct

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-26 : 00:07:21

Not sure what the "1"s are doing in your data (Lee1? Brook1?), but if you use REPLACE() to clear these out, then you can use this function to parse the name strings:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499
The syntax would be:
dbo.FormatName([YourString], 'F') as FNAME,
dbo.FormatName([YourString], 'm') as MI,
dbo.FormatName([YourString], 'L') as LNAME