insert comma between lastnames

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)

insert comma between lastnames

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-14 : 14:07:01

I want to convert 'del Toro Castro' into 'del Toro', 'Castro'

I made this function

CREATE FUNCTION splitLastnames (@lastname as nvarchar(24))

RETURNS nvarchar(30) AS  
BEGIN 
	Declare @lastname1 char(12)
	Declare @lastname2 char(12)
	Declare @pos            tinyint
	
	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,''))
	Return '''' + rtrim(@lastname1)+''', '''+ rtrim(@lastname2) + ''''
END

and I get this 'del Toro', 'del Toro Cas'
but when I run this

Declare @lastname as char(24)
set @lastname = 'del Toro Castro'

    Declare @lastname1 char(12)
	Declare @lastname2 char(12)
	Declare @pos            tinyint
	
	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,''))

Print @lastname1

Print @lastname2

in the QA I get this
del Toro
Castro

How should I modify the function

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-14 : 14:45:15

Never mind I solve it I changed the data type of @lastname1 and @lastname2

CREATE FUNCTION splitLastnames (@lastname as nvarchar(24))

RETURNS nvarchar(30) AS  
BEGIN 
	Declare @lastname1 nvarchar(12)
	Declare @lastname2 nvarchar(12)
	Declare @pos            tinyint
	
	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,''))
	Return '''' + rtrim(@lastname1)+''', '''+ rtrim(@lastname2) + ''''
END

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources