help quick on split[RESOLVED] - 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)

help quick on split[RESOLVED]

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-05 : 15:57:24

If I have a name that I need to split, how would I do that?

Like:

Norman Robert

Like until you hit the first space is the last name, and the rest is the first name.

like this:


UPDATE Table
??????

I want to insert the first and last name into brand new columns.

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-05 : 16:21:05

maybe something like this:

select substring(FirstPerson,1,) from tblwebwhacker

but not substring. What else can I use?

If anyone knows, please let me know. My boss needs this fast! Thanks!

Brenda

If it weren't for you guys, where would I be?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-05 : 16:43:48

This should do it


Declare @name varchar(100)
Set @name = 'Corey Aldebol'

Select 
	LastName = left(@name,charindex(' ',@name)-1), 
	FirstName = right(@name,len(@name) - charindex(' ',@name))

Corey

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-05 : 16:48:22

I did this:

Update tblwebwhacker
SET LastName = left(FirstPerson,charindex(' ',FirstPerson)-1), FirstName = right(FirstPerson,len(FirstPerson) - charindex(' ',FirstPerson))

But it said this:
Invalid length parameter passed to the substring function.

Any ideas?

Brenda

If it weren't for you guys, where would I be?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-05 : 16:58:38

Yeah... if you have something that doesn't have a 'space' in it, or the column is null.

add a condition to your update query, something like:

Where
...
FirstPerson like '% %'
and FirstPerson is not null

Corey

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-05 : 17:10:15

This works to get the first name:

set FirstName = substring(FirstPerson,charindex(' ',FirstPerson)+1 ,len(FirstPerson))

How would I do the last name?

It comes like this: Lastname Firstname

Thanks!

Brenda

If it weren't for you guys, where would I be?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-06 : 00:22:55

I gave you an example of how to get both... look up

Corey

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-08 : 10:10:54

I finally got it all done. THanks!

Update tblwebwhacker
set Lastname = substring(FirstPerson, 1, charindex(' ', FirstPerson) -1)
set FirstName = substring(FirstPerson,charindex(' ',FirstPerson)+1 ,len(FirstPerson))

Brenda

If it weren't for you guys, where would I be?

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-08 : 14:59:41

I had a similar situation but the name had to be splited in 4 fields (two lastnames, firstname and initial)
with the little catch that last names included spaces
for example
Del Toro
Mc Millan

I made a loop to split the two lastnames
something like

Declare @lastname as char(25)
Declare @lastname1 as char(12), @lastname2 as char(12)
Declare @pos as int

set @lastname = 'del Toro Castro'
set @pos = charindex(' ', @lastname)
set @lastname1 = left(@lastname, @pos) 

While @lastname1 in ('del', 'de','de los', 'de la', 'la', 'Mc')
Begin
    set @pos = charindex(' ', @lastname, @pos+2)
    set @lastname1 = left(@lastname, @pos)
End 

Set @lastname2 = ltrim(replace(@lastname, @lastname1,''))

can this be done without the loop?

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-08 : 15:44:50

sure... with a tally table:


Declare @tally table (n int identity(0,1), blah int)
Insert Into @tally
Select null From master.dbo.syscolumns

Declare @Name varchar(100)
set @name = 'Bill del Toro Castro'

Select 
	@name,
	n,
	isnull(nullif(charindex(' ',@name,n+1),0),len(@name)),
	substring(@name,n+1,isnull(nullif(charindex(' ',@name,n+1),0),len(@name))-n)
From @tally A
Where (substring(@name,n,1)=' '
and n <= len(@name))
or n = 0

Corey

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-09 : 08:51:05

That was realy nice but your results are not exactly correct
in most latin cultures the name is composed of a
firstname,
initial,
lastname1(father's lastname), and
lastname2(mother's maiden name)

the first word before the first space is Firstname
if there is a period or before the next space there is only one character that would be the initial
but lastnames that is not so easy because they contain spaces in your example
'Bill del Toro Castro'
should be splited as
firstaname = 'Bill'
lastname1 = 'del Toro'
lastname2 = 'Castro'

and 'Raúl F. Mederos de la Rosa'
should be splited as
firstname = 'Raúl'
initial = 'F'
lastname1 = 'Mederos'
lastname2 = 'de la Rosa'

So that was realy my question if there is a way where I can split
Mederos de la Rosa into
lastname1 = 'Mederos'
lastname2 = 'de la Rosa' and
del Toro Castro into
lastname1 = 'del Toro'
lastname2 = 'Castro'
Without using a loop as I did

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources