Substring Name Column - 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 2012 Forums

Transact-SQL (2012)

Substring Name Column

Author

Topic

beatkeeper25
Starting Member

27 Posts

Posted - 2015-02-18 : 10:14:21

I need to divide a name column into first and last name. The field comes in with just spaces between names (some have middle initial).
(ex: FLOURNIE JOHN C
CHAO MARK)

I am able to grab last name with
LEFT([Account Name], CHARINDEX(' ', [Account Name]) - 1) as LastName.

Having trouble with first name. How does CHARINDEX know which black space you are referring to when there is more than one?

This is bringing back weird results:
RIGHT([Account Name], CHARINDEX(' ',[Account Name]) +1).

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 10:48:21

" How does CHARINDEX know which black space you are referring to when there is more than one"

It takes the first one.

Some things to note:

1. If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.

That means your expression could fail with

Msg 536, Level 16, State 1, Line 7
Invalid length parameter passed to the left function.

2. What about compound surnames (e.g. van Dam, van der Plaats, or García-Carrión Martínez)?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-18 : 15:19:57

[code]
SELECT
LEFT([Account Name], CHARINDEX(' ', [Account Name]) - 1) as LastName,
RIGHT([Account Name], LEN([Account Name]) - CHARINDEX(' ',[Account Name])) AS FirstName --rest of column
FROM (
SELECT 'FLOURNIE JOHN C' AS [Account Name] UNION ALL
SELECT 'CHAO MARK'
) AS test_data

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources