分割字符串时出现问题 [英] Problem in splitting the string

问题描述

你好！

我在表中有一列app_Id.

它的值类似于

10/2000

10/2001

17/2001

32/2009

89/2011

我要做的是对app_id进行排序. "/"之前的部分是申请号，而"/"之后的部分是年份.
所以我想进行排序，我应该在数据表中为appNumber和year分别创建两列.
然后我用这个查询

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

现在我面临的问题是，对于

10/2000

这样的app_id，我得到Number = 10和year = 2010，这是正确的，但是对于某些般的app_id值，我却得到number = 5，但year = 011，对于像

378/2011

这样的app_id，我得到的号码= 378，但是Year =/2011.

请告诉我如何解决此逻辑错误.您必须使用相同的逻辑

 创建 表临时
(id  varchar ( 50 ))

插入 插入 temp 值('  10/2000')
插入 插入 temp 值('  5/2000')
插入 插入 temp 值('  345/2000')

选择 SUBSTRING(id， 1 ，charindex(' /'，id， 0 )-1)作为 ID，
SUBSTRING(id，charindex(' /'，id， 0 )+ 1，LEN(id)-1)按年来自的温度

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

解决方案

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

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

这篇关于分割字符串时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！