Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL-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 21: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
0 DamithSL 335
1 OriginalGriff 209
2 BillWoodruff 170
3 Zoltán Zörgő 165
4 Maciej Los 123
0 OriginalGriff 7,854
1 DamithSL 5,949
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,084
4 Kornfeld Eliyahu Peter 4,539


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100