Click here to Skip to main content
13,088,101 members (73,078 online)
Rate this:
Please Sign up or sign in to vote.
See more:

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

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
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
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 ''
ujjwal uniyal 4-Oct-12 3:40am
Thanks Santhosh.. your answer was helpful as always :)
Santhosh Kumar J 4-Oct-12 3:43am
Rate this: bad
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' + '%'

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.170813.1 | Last Updated 4 Oct 2012
Copyright © CodeProject, 1999-2017
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