help with 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

General SQL Server Forums

New to SQL Server Programming

help with substring

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2006-12-01 : 12:15:00

i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment:
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST

but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code

select ActPrjMgr
,ActEmpId
,ActEmpName
,ActCustName
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
,ActPrjCode
,left(ActPrjType,2) as Status
,ActEmpTaskCode
,left(ActBillingPeriod,11)as ppedate
,left(ActivityDate,11) as actdate
,ActTimevalue
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and ActBillingPeriod = '11/17/2006'
and actprjcode <> ' '
and actprjcode is not null
--and ActBillingPeriod = @StartDate
order by ActPrjMgr
,ActEmpID
,ActEmpTaskCode
,ActivityDate

Sabin_33
Starting Member

2 Posts

Posted - 2006-12-01 : 13:06:05

Change your Right to
[CODE]
SELECT SUBSTRING(ActCustName,CHARINDEX(',',ActCustName,(CHARINDEX(',',ActCustName)+1),LEN(ActCustName)) as [LastName]
[/CODE]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 13:07:02

or:

This could work if lastname is always the right most word. But if there are suffixs?


select case 
	when charindex(' ', ActCustName) > 0 
	 then right(ActcustName, charindex(' ', reverse(ActCustName))-1)
	 else ActCustName
	end
from	(
	select 'Hennry Jones' ActcustName union all
	select 'Hennry t. Jones' union all
	select 'Hennry (Don''t eat no meat) Jones' union all
	select 'thurston howell III' union all
	select 'sammy davis jr.' union all
	select 'cher'
	) a

output:

Jones
Jones
Jones
III
jr.
cher

Be One with the Optimizer
TG

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-01 : 13:16:21

Hi,

I found this function, which gives you the last index of an occurance

http://wiki.ittoolbox.com/index.php/Code:User-Defined_string_Functions_Transact-SQL_MS_SQL_Server

-------------------------
CREATE function RATC (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000), @nOccurrence smallint = 1 )
returns smallint
as
begin
if @nOccurrence > 0
begin
declare @i smallint, @length smallint, @StartingPosition smallint
select @length = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @cSearchExpression = lower(reverse(@cSearchExpression)), @cExpressionSearched = lower(reverse(@cExpressionSearched))
select @i = 0, @StartingPosition = -1
while @StartingPosition <> 0 and @nOccurrence > @i
select @i = @i + 1, @StartingPosition = charindex(@cSearchExpression COLLATE Latin1_General_CI_AS,
@cExpressionSearched COLLATE Latin1_General_CI_AS, @StartingPosition + 1)
if @StartingPosition <> 0
select @StartingPosition = 2 - @StartingPosition + @length - datalength(@cSearchExpression)/(case SQL_VARIANT_PROPERTY(@cSearchExpression,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
end
else
set @StartingPosition = NULL

return @StartingPosition
end
GO
------------------------------------------------

which seems to work

insert into testname select 'First Middle second third Last'

select RIGHT(ActCustName,LEN(ActCustName) - dbo.RATC(' ',ActCustName,1) ) AS LAST from testname

gives the result
'Last'

Sean

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 14:00:06

quote:
Originally posted by Sean_B

Hi,

I found this function, which gives you the last index of an occurance

http://wiki.ittoolbox.com/index.php/Code:User-Defined_string_Functions_Transact-SQL_MS_SQL_Server

-------------------------
CREATE function RATC (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000), @nOccurrence smallint = 1 )
returns smallint
as
begin
if @nOccurrence > 0
begin
declare @i smallint, @length smallint, @StartingPosition smallint
select @length = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @cSearchExpression = lower(reverse(@cSearchExpression)), @cExpressionSearched = lower(reverse(@cExpressionSearched))
select @i = 0, @StartingPosition = -1
while @StartingPosition <> 0 and @nOccurrence > @i
select @i = @i + 1, @StartingPosition = charindex(@cSearchExpression COLLATE Latin1_General_CI_AS,
@cExpressionSearched COLLATE Latin1_General_CI_AS, @StartingPosition + 1)
if @StartingPosition <> 0
select @StartingPosition = 2 - @StartingPosition + @length - datalength(@cSearchExpression)/(case SQL_VARIANT_PROPERTY(@cSearchExpression,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
end
else
set @StartingPosition = NULL

return @StartingPosition
end
GO
------------------------------------------------

which seems to work

insert into testname select 'First Middle second third Last'

select RIGHT(ActCustName,LEN(ActCustName) - dbo.RATC(' ',ActCustName,1) ) AS LAST from testname

gives the result
'Last'

Sean

sorry to call you out, Sean, but even your statement that calls the function is more complicated than the statement I used to do the work of the function.

(from above):

right(ActcustName, charindex(' ', reverse(ActCustName))-1)

But either solution won't solve the problem of suffixs

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 09:51:46

Also read about parse name in sql server help file

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 09:59:44

Hello Igor!

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 10:15:45

quote:
Originally posted by Peso

Hello Igor!

Peter Larsson
Helsingborg, Sweden

More clever than you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources