Problem with substring

Question

0.00/5 (No votes)

See more:

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

ujjwal uniyal

Add a Solution

2 solutions

Solution 1

I think u use 'like' clause

try like that :

select * from action_taken where sender_index_code like 'your data' + '%'

Posted 3-Oct-12 20:54pm

solanki.net

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Kumar Jayaraman · Accepted Answer · 2012-10-03T21:29:00

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

SQL

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