Problem in splitting the string

Question

0.00/5 (No votes)

See more:

Hello!!

I have a column app_Id in a table.

It has values like

10/2000

10/2001

17/2001

32/2009

89/2011

What i have to do is sort app_id. Part before '/' is the application number and after '/' is year.
so i thought to sort i should make two columns in my datatable each for appNumber and year.
Then i used this query

SELECT [app_id], (Select LEFT(app_Id, CHARINDEX('/',app_Id )-1)) as Number,(Select Right(app_Id, CHARINDEX('/',app_Id )+1)) as Year ,[app_type], convert(varchar,[app_date],103) as app_date, [subject], [app_name], [gar_name], [source_vill], [source_place], [source_dist], [mode], [epat_letno],[status] ,Approved  FROM [viv_app]  where FinancialYear <>'2012-2013'  and Status='Sent to treasury' order by [Year] desc ,Number DESC

Now the problem that i am facing is that for app_id like

10/2000

i am getting Number = 10 and year =2010 which is right but for some values of app_id like

5/2011

i am getting number =5 but year =011 and for app_id like

378/2011

i am getting number =378 but Year= /2011.

please tell me how to solve this logical error

Posted 9-Aug-12 2:22am

ujjwal uniyal

Add a Solution

1 solution

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-08-09T02:33:00

Solution 1

You try this and let me know. You have to use the same logic

SQL

create table temp
(id varchar(50))

insert into temp values ('10/2000')
insert into temp values ('5/2000')
insert into temp values ('345/2000')

select SUBSTRING(id,1,charindex('/',id,0)-1) as id,
SUBSTRING(id,charindex('/',id,0)+1,LEN(id)-1) as year from temp

Posted 9-Aug-12 2:33am

Santhosh Kumar Jayaraman

Comments

StianSandberg 9-Aug-12 8:35am

5'd :)

Santhosh Kumar Jayaraman 9-Aug-12 8:36am

Thanks:)

ujjwal uniyal 9-Aug-12 8:36am

It works as i want it to work . But will you please tell me how it's working?? I am having difficulty understanding this. there is no point in just copying the answer

StianSandberg 9-Aug-12 8:40am

Good to see someone who want to actually understand the answer :)
I'm sure Santhosh will give a good explanation. His answer is perfect..

Santhosh Kumar Jayaraman 9-Aug-12 8:42am

ya thanks. I think the below explanation might help him

Santhosh Kumar Jayaraman 9-Aug-12 8:41am

SUBSTRING(id,1,charindex('/',id,0)-1)

Am doing substring in id column from 1st index to the left of symbol /.
Charindex function i am passing which symbol i want to identify in column Id and 0 is for the index. This charindex will give me the position o /. But i want one place before this symbol. So am subtracting 1. thats will be the end position of the first substring.

Similarly for getting year.i want start position as the place next to symbol /. So this time am using charindex at start position of substring. And we need one place after that , so +1 there and till the end of string.. So len(id)-1 will give last index

Santhosh Kumar Jayaraman 9-Aug-12 8:42am

Please let me know if the explanation is not clear.

ujjwal uniyal 9-Aug-12 8:44am

Thanks Santhosh. i now understand what's happening here. :)

Santhosh Kumar Jayaraman 9-Aug-12 8:45am

Welcome..