Splitting Names - 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

General SQL Server Forums

New to SQL Server Programming

Splitting Names

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2010-04-15 : 01:37:46

I need to split a column of data being the first names of the clients so that the first and second name are now in 2 columns rather then 1 column. I have been able to do this however if the client only has a single name I get an error message.

If I run
select givennames, surname
, firstname = left(givennames,charindex(' ',givennames)-1)
,secondname = right(givennames,charindex(' ',reverse(givennames))-1)
from client
where givennames is not null and givennames like 'william john'

I get the required result however if I delete the 'and givennames like 'William john'' and it uses the data and finds the first name with only one name it returns an error

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

Any suggestions on how to get around this?
Also if there are 3 first names I get the last and the first but not the middle name.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 01:58:14

One way:


declare @given varchar(255)
set @given = 'Web'
select left(@given,case when charindex(' ',@given)-1 >0 then charindex(' ',@given)-1 else len(@given) end)

But are there really no names with more than two parts?

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 05:15:38

quote:
Originally posted by webfred
But are there really no names with more than two parts?

From the OP. Also if there are 3 first names I get the last and the first but not the middle name.

Question: How do you want 3 part names to be split. eg, How do you split the name 'Lee Harvey Oswald'? I would think 'Lee Harvey, and 'Oswald', but you're post wasn't that specific.

If so, you need to Reverse the string, split it on the first space, then reverse the results. Either that, of you'll need to use a function.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 05:28:45

This might work also (assuming only 2 names)


SELECT
	givenNames
	, surname
	, givenNames AS [Firstname]
	, '' AS [SecondName]
FROM
	client
WHERE
	givenNames NOT LIKE '% %'

UNION ALL SELECT
	givenNames
	, surname
	, LEFT(givennames,charindex(' ',givennames)-1) AS [Firstname]
	, right(givennames,charindex(' ',reverse(givennames))-1) AS [Surname]
FROM
	client
WHERE
	giveNames LIKE '% %'

I think it would be slower than webfred's though as it requires at least 2 table scans compared to at least one for webfreds. However it may not require quite as many string operations.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 05:40:49

Another possibility:


declare @testname varchar(255)
set @testname = 'Lee Harvey Oswald'
select
parsename(replace(@testname,' ','.'),1) as secondname,
isnull(parsename(replace(@testname,' ','.'),3)+' ','')+parsename(replace(@testname,' ','.'),2) as firstname

set @testname = 'Lee Oswald'
select
parsename(replace(@testname,' ','.'),1) as secondname,
isnull(parsename(replace(@testname,' ','.'),3)+' ','')+parsename(replace(@testname,' ','.'),2) as firstname

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 05:51:50

Suggest not using REVERSE - i.e. find the first space and use that as the point for both the LEFT and the RIGHT part (then nothing can be lost if there are multiple spaces)
Then use NullIf to "kill" the firstname if there are no spaces


SELECT	MyData
	, firstname = left(MyData, NullIf(charindex(' ',MyData)-1, -1))
	, secondname = right(MyData, DATALENGTH(MyData) - charindex(' ',MyData))
FROM
(
	SELECT 'Fred Bloggs' AS MyData
	UNION ALL SELECT 'OneName'
	UNION ALL SELECT 'One Two Three'
) AS X

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 05:53:31

Need to be careful about leading / training spaces, so maybe use LTrim, RTrim on the column before it is used by the various functions.

Then can use LEN() instead of DATALENGTH() (particularly as DATALENGTH() will give bytes, not characters, for Nvarchar)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 06:08:57

quote:
Originally posted by Kristen

Need to be careful about leading / training spaces, so maybe use LTrim, RTrim on the column before it is used by the various functions.

Then can use LEN() instead of DATALENGTH() (particularly as DATALENGTH() will give bytes, not characters, for Nvarchar)

With all these functions being called multiple times on the one string, it might be worth passing the string off to a UDF, where these functions can be called only once, and then cached. eg, if you use RTrim and LTrim, then you need to call both of them every time you reference the field. If you use a UDF, then you can clean the field once with RTrim and LTrim, and and then use the clean copy.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 06:13:03

quote:
Originally posted by DBA in the making

quote:
Originally posted by Kristen

Need to be careful about leading / training spaces, so maybe use LTrim, RTrim on the column before it is used by the various functions.

Then can use LEN() instead of DATALENGTH() (particularly as DATALENGTH() will give bytes, not characters, for Nvarchar)

With all these functions being called multiple times on the one string, it might be worth passing the string off to a UDF, where these functions can be called only once, and then cached. eg, if you use RTrim and LTrim, then you need to call both of them every time you reference the field. If you use a UDF, then you can clean the field once with RTrim and LTrim, and and then use the clean copy.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Or a nested group of derived tables? Same kind of idea.

Les.61 -- ~~don't~~ worry about all of this. it's getting kinda technical. But important because you don't want a sql exception for stuff like this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 06:19:44

"eg, if you use RTrim and LTrim, then you need to call both of them every time you reference the field."

I would use a derived table, as TC says. Not sure its complicated, it it?


FROM client

becomes


FROM client AS C
    JOIN
    (
        SELECT [TrimmedID] = C2.SomeID
               [TrimmedGivenNames] = LTrim(RTrim(givenNames))
        FROM client AS C2
    ) AS C2
        ON C2.TrimmedID = C.SomeID

and then just used the [TrimmedGivenNames] column wherever [givenNames] would otherwise be used (i.e. in functions etc.)

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 06:25:43

P.S. I don't think it should be ignored as any leading space(s) will royally-roger the outcome

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 06:39:20

quote:
Originally posted by Kristen

P.S. I don't think it should be ignored as any leading space(s) will royally-roger the outcome

Agreed -- edited earlier post.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources