ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决sql server 2008 - Invalid length parameter passed to the LEFT or SUBSTRING function

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:SQL Server 2008 function types in T-SQL

SQL Server 2008 function types in T-SQL from: http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1327000,00.html   15 Oct 2008 | SearchS

I've seen a few of these questions asked but haven't spotted one that's helped!! I'm trying to select the first part of a postcode only, essentially ignoring anything after the space. the code i am using is

SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode ) -1)

however i am getting Invalid length parameter passed to the LEFT or SUBSTRING function! There's no nulls or blanks but there are some the only have the first part. is this what causing the error and if so whats the work around?!

sql-server-2008
|
  this question
asked Jun 28 '13 at 15:14 GPH 569 2 11 36

 | 

3 Answers
3

解决方法

That would only happen if PostCode is missing a space. You could add conditionality such that all of PostCode is retrieved should a space not be found as follows

select SUBSTRING(PostCode, 1 ,
case when  CHARINDEX(' ', PostCode ) = 0 then LEN(PostCode) 
else CHARINDEX(' ', PostCode) -1 end)

|
  this answer
answered Jun 28 '13 at 15:19 iruvar 15.4k 3 28 60

 | 

CHARINDEX will return 0 if no spaces are in the string and then you look for a substring of -1 length.

You can tack a trailing space on to the end of the string to ensure there is always at least one space and avoid this problem.

SELECT SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode + ' ' ) -1)

|
  this answer
edited Jul 28 '16 at 8:01 answered Jun 28 '13 at 15:21 Martin Smith 282k 43 452 531

 | 

This is because the CHARINDEX-1 is returning a -ive value if the look-up for " " (space) is 0. The simplest solution would be to avoid '-ve' by adding

推荐:SQL Server 2008 的安装

               SQL Server 2008简体中文企业版下载(SQL2008)   SQL Server 2008分为SQL Server 2008企业版、标准版、工作组版、Web版、开发者版、Express版、Co

ABS(CHARINDEX(' ', PostCode ) -1))

which will return only +ive values for your length even if CHARINDEX(' ', PostCode ) -1) is a -ve value. Correct me if I'm wrong!


|
  this answer
edited Jan 12 '15 at 15:19 answered Jan 12 '15 at 14:37 ajufsd 59 1 11      Please explain in what cases CHARINDEX is negative –  fjuan Jan 12 '15 at 15:01

 | 

推荐:Sql Server 2008

创建函数: create function sector_pid(@id varchar(10)) returns @t_level table(id varchar(10)) as begin   insert into @t_level select @id   select @id 


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。