像substring传递了无效的length函数

Question

select aaa, substring(aaa,patindex('%<[a-q]%',substring(aaa,1,datalength(内aaa))),patindex('%">%',substring(aaa,1,datalength(aaa)))+2-patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))))
    , stuff(substring(aaa,1,datalength(aaa)),patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),
                            patindex('%">%',substring(aaa,1,datalength(aaa)))+2-patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),' ')
     , substring(aaa,patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),datalength(aaa))
from  abc
where aaa like '%<[a-q]%' 
  我执行这条语句时  提示我 传递了无效的length 参数   就在上一次执行的时候 还执行的 好好的  再点一下 就不行了  为什么啊？  跪求各位大神帮忙啊……  感激不尽  新人 求指点……substringselectpartindexsql

Answer 1

如果你什么都没改，上次能运行，这次不行，那证明你的数据出现异常，使得patindex定位不了，建议你先检查一下你的数据是否有异常或者不希望出现的数据

Answer 2

President: Mr P. G. Harlech Jones</p> Past-President: Mr C. Padgett</p> President-Elect: Mr R. Hargreaves</p> 7 Mansfield Street,</p> <p id="p-5">London W1G 9NQ</p> <p id="p-6">Telephone [phone]</p> <p id="p-7">Fax [phone]</p> <p id="p-8">e-mail: [email protected]</p> <p id="p-9">www.bva.co.uk</p> <p id="p-10">BVA members … </p>  我的 数据 是这样的  我就想把中间的<p id="p-5">这样的标签 去掉 跟之前的数据 也没什么不一样的啊……

Answer 3

除了你代码里面多了个“内”字我去掉了之后，用你的数据我直接可以得到结果啊select aaa, substring(aaa,patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),patindex('%">%',substring(aaa,1,datalength(aaa)))+2-patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))))
    , stuff(substring(aaa,1,datalength(aaa)),patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),
                            patindex('%">%',substring(aaa,1,datalength(aaa)))+2-patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),' ')
     , substring(aaa,patindex('%<[a-q]%',substring(aaa,1,datalength(aaa))),datalength(aaa))
from  abc
where aaa like '%<[a-q]%' /*
aaa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
President: Mr P. G. Harlech Jones</p> Past-President: Mr C. Padgett</p> President-Elect: Mr R. Hargreaves</p> 7 Mansfield Street,</p> <p id="p-5">London W1G 9NQ</p> <p id="p-6">Telephone [phone]</p> <p id="p-7">Fax [phone]</p> <p id="p-8">e-mai <p id="p-5">                                                                                                                                                                                                                                                     President: Mr P. G. Harlech Jones</p> Past-President: Mr C. Padgett</p> President-Elect: Mr R. Hargreaves</p> 7 Mansfield Street,</p>  London W1G 9NQ</p> <p id="p-6">Telephone [phone]</p> <p id="p-7">Fax [phone]</p> <p id="p-8">e-mail: bvahq@bv <p id="p-5">London W1G 9NQ</p> <p id="p-6">Telephone [phone]</p> <p id="p-7">Fax [phone]</p> <p id="p-8">e-mail: [email protected]</p> <p id="p-9">www.bva.co.uk</p> <p id="p-10">BVA members … </p>  */