getting error while running a query

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

getting error while running a query

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-10-24 : 12:46:12

Hi All,

I have the following query

SELECT 'GE:13' as Education
case when Education like '%GE%' then
substring(Education, charindex('GE', Education)+3, charindex(' ', Education+' ', charindex('GE', manhour))-charindex('GE', Education)-3)
end as GE
from tblTest

basically I am trying to put value 13 under column GE

I am getting an error "Invalid length parameter passed to the substring function.

There are 1 million record in this table and most of them are parsed by the above query except few of them are throwing this error.I am unable to figure out what is different with this above value 13. Most of the values are 1 digit, 2 digits or 3 digits for e.g GE:123, GE12, GE1

any help will be grealy appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 12:57:22

i think its because one or more of fields specified inside CHARINDEX does not have occurance of that character. try


SELECT 'GE:13' as Education
case when Education like '%GE%' then
substring(Education, charindex('GE', Education + 'GE')+3, charindex(' ', Education+' ', charindex('GE', manhour + 'GE'))-charindex('GE', Education + 'GE')-3)
end as GE
from tblTest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-10-24 : 13:01:10

still getting the same error. Basically this is what I am trying to do

I have the data below in col1

col1
AB:5
AB:9 EF:5
EF:180
CD:12 AB:12


I want top parse it as below.



Col1               AB       CD       EF
AB:5               5
AB:9 EF:5          9                 5
EF:180                              180
CD:12 AB:12        12       12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 13:59:27

if format is consistent i would have used a string parsing UDF for this as


SELECT MAX(CASE WHEN Cat ='AB' THEN Val END) AS [AB],
MAX(CASE WHEN Cat ='CD' THEN Val END) AS [CD],
MAX(CASE WHEN Cat ='EF' THEN Val END) AS [EF]
FROM
(
SELECT LEFT(f.Val,CHARINDEX(':'.f.Val)-1) AS Cat,
STUFF(f.Val,1,CHARINDEX(':'.f.Val),'') AS Val
FROM Table t
CROSS APPLY dbo.ParseValues(t.Col1,' ')f
)t


ParseValues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources