Click here to Skip to main content
15,907,687 members
Please Sign up or sign in to vote.
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

1 solution

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
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900