Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NETSQL-Server
Hello!!
 
I have a column name sender_index_code in a table called action_taken. now i ma trying to search into the table using sender_index_code.
 
Some values of sender_index_code are like
5/2012-D-prog
7/2011-U-sec
16/2012-R-secids
 
now i am letting user enter only the number part and search the table like when user enter 5/2012 he will be shown the records for the sender_index_code 5/2012-D-prog.
 
for this i am using this query
 
 select * from action_taken where  substring(sender_index_code,1,charindex('-',sender_index_code,0)-1) ='5/2012'
 
but it shows me error that
Invalid length parameter passed to the LEFT or SUBSTRING function.
 
how ever when i am breking this query into two pasts for test purpose in my sql server they are working fine.
 
for example i first find the char index of '-'.
select CHARINDEX ('-','5/2012-D-prog',0)-1
here i have used -1 because i want to get values fore the '-'
 
then use this char index to extract string before the first '-'
 
select SUBSTRING('5/2012-D-prog',1,6)
 
abd it gives me the right answer i.e 5/2012 but the whole query as a whole is not working
Posted 3-Oct-12 20:45pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Thats because if charindex is not present then substring will become 0-1 =-1 but the index itself starts from 0 and no element would be at -1 index.
 
For eg, if 5/2012D is one of the value, then in substring function it becomes
substring(sender_index_code,1,(0-1)) and it will throw error
try
select * from action_taken where '5/2012'=
 CASe when charindex('-',sender_index_code,0)>0 then  substring(sender_index_code,1,charindex('-',sender_index_code,0)-1)
else ''
end
  Permalink  
v3
Comments
ujjwal uniyal at 4-Oct-12 3:40am
   
Thanks Santhosh.. your answer was helpful as always :)
Santhosh Kumar J at 4-Oct-12 3:43am
   
welcome
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I think u use 'like' clause
 
try like that :
 
select * from action_taken where sender_index_code like 'your data' + '%'
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,990
1 OriginalGriff 558
2 Manas Bhardwaj 383
3 Tadit Dash 335
4 Damith Weerasinghe 330
0 Sergey Alexandrovich Kryukov 9,575
1 OriginalGriff 6,006
2 Peter Leow 4,500
3 Maciej Los 3,540
4 Abhinav S 3,513


Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 4 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid