June 21, 2006 at 3:51 pm
Hi guys...I know this is a common question, but I've got a twist.
I've got a column like this:
smith, joe
obviously i want an lname and rest columns.
I wrote this script....
SELECT LEFT(new_contributor,CHARINDEX(',',new_contributor) - 1) AS [LNAME],
RIGHT(new_contributor,LEN(new_contributor) - CHARINDEX(' ',new_contributor)) AS [Rest]
But I keep getting this message:
(7 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
Help?
June 21, 2006 at 3:56 pm
Any time you use the LEFT()/RIGHT()/SUBSTRING() functions in conjuntion with CHARINDEX(), you need to ensure that CHARINDEX() is not spitting back zero as a result. In your code, you're subtracting 1 from the CHARINDEX() result, which if zero, will yield a negative which is an "Invalid length parameter passed to the substring function".
It is often better to do this via a user defined function where you can capture the CHARINDEX() result into a local variable and validate it before using it in subsequent string operations.
Alternatively, use a WHERE clause so that you only attempt to parse a name if it contains a comma followed by a space.
WHERE new_contributor LIKE '%, %'
June 21, 2006 at 4:02 pm
create
table #tmp
(
new_contributor varchar(50))
insert
into #tmp values('Test, Mine')
insert
into #tmp values('Tester, Mine')
--insert into #tmp values('Test Mine')
insert
into #tmp values('Test Mine,')
insert
into #tmp values('Test, Mi,ne')
SELECT
LEFT(new_contributor,CHARINDEX(',',new_contributor) - 1) AS [LNAME],
RIGHT(
new_contributor,LEN(new_contributor) - CHARINDEX(' ',new_contributor)) AS [Rest]
from
#tmp
drop
table #tmp
the commented out line caused that error
This works:
SELECT
case
when CHARINDEX(',',new_contributor) > 0 then
LEFT(new_contributor,CHARINDEX(',',new_contributor) - 1)
else
new_contributor
end
as [LNAME],
case
when CHARINDEX(',',new_contributor) > 0 then
RIGHT(new_contributor,LEN(new_contributor) - CHARINDEX(' ',new_contributor))
else NULL
end
AS [Rest]
from
#tmp
June 21, 2006 at 4:51 pm
Just noticed another bug. You parse based upon two different charindexes. In one case you're looking for ',', in the other you're looking for ' '. What of the last name is 'Mac George'? Or, how I found it, what if there is a space but no comma?
One fix listed below:
LTRIM
(RIGHT(new_contributor,LEN(new_contributor) - CHARINDEX(',',new_contributor)) )
June 22, 2006 at 12:10 pm
Where would I put that fix?
i'm using this script right now.
SELECT
case when CHARINDEX(',',new_contributor) > 0 then
LEFT(new_contributor,CHARINDEX(',',new_contributor) - 1)
else
new_contributor
end as [LNAME],
case when CHARINDEX(',',new_contributor) > 0 then
RIGHT(new_contributor,LEN(new_contributor) - CHARINDEX(' ',new_contributor))
else NULL
end AS [Rest]
from #tmp
June 22, 2006 at 12:17 pm
SELECT
case when CHARINDEX(',',new_contributor) > 0 then
LEFT(new_contributor,CHARINDEX(',',new_contributor) - 1)
else
new_contributor
end as [LNAME],
case when CHARINDEX(',',new_contributor) > 0 then
--Change here
LTRIM(RIGHT(new_contributor,LEN(new_contributor) - CHARINDEX(',',new_contributor)) )
--End of change
else NULL
end AS [Rest]
from #tmp
June 22, 2006 at 8:18 pm
You can use the function I wrote to parse the name (get it here: UDF: Parse a delimited list of paramters )
Then your statement should look like this:
select (select vcParameters from dbo.fnParseParamstring('smith, joe',',') where iRowId = 1) lName
,(select ltrim(vcParameters) from dbo.fnParseParamstring('smith, joe',',') where iRowId = 2) Rest
retruns:
lName Rest
------- -----------------
smith joe
(1 row(s) affected)
also, your actual statement will be:
select (select vcParameters from dbo.fnParseParamstring(new_contributor,',') where iRowId = 1) lName
,(select ltrim(vcParameters) from dbo.fnParseParamstring(new_contributor,',') where iRowId = 2) Rest [From your table where...]
-
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply