How to Order desc/asc by using string in a column ??

I have a table like this ,

tbl1
id      type         order_str         Name
1      1           1#1#1#1#1        onetype
2      2           1#2#1#1#1        second
3      2           1#3#1#1#1        first
4      3           1#3#1#1#1        thirdvalueSECOND
5      3           1#3#2#1#1        thirdvaluefirst


if type = 2 , i need order by like this

if its desc,
id  name       type   order_str       required_value
3   first            2     1#3#1#1#1       3                                           >>using second index of order_str
2   second       2     1#2#1#1#1       2                                        >>using second index of order_str

IF ITS type = 3 if its desc
id  name                      type   order_str       required_value
5   thirdvaluefirst            3     1#3#2#1#1       2                      >>using third index of order_str
4   thirdvalueSECOND   3     1#3#1#1#1       1
My table :
CREATE TABLE [dbo].[tbl1](
    [id] [int] NOT NULL,
    [type] [nvarchar](50) NULL,
    [name] [nvarchar](50) NULL,
    [order_str] [nvarchar](30) NULL
) ON [PRIMARY]
GO
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(1,'1','onetype','1#1#1#1#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(2,'2','second','1#2#1#1#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(3,'2','first','1#3#1#1#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(4,'3','thirdvalueSECOND','1#3#1#1#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(5,'3','thirdvaluefirst','1#3#2#1#1')

select * from tbl1

Open in new window

LVL 18
Rajar AhmedConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Om PrakashConnect With a Mentor Commented:
select [type], right(order_str, len([order_str]) - ([type]*2 - 2 )),order_str,   * from tbl1
order by len([order_str]) - ([type]*2 - 2)
0
 
Rajar AhmedConsultantAuthor Commented:
hi om_prakash_p,


Just changed a bit ur query,
select id,[type], right(order_str, len([order_str]) - ([type]*2 - 2 ))as required_value,order_str,name   from tbl1
where type = 2
order by len([order_str]) - ([type]*2 - 2) desc

Output was like dis
id    type    required_value order_str    name
2       2        2#1#1#1           1#2#1#1#1    second
3        2        3#1#1#1            1#3#1#1#1    first

But i want Like this ,if its desc
id    type     required_value  order_str    name
2        2        3                         1#3#1#1#1    first
3        2        2                        1#2#1#1#1    second

if its asc,
id    type     required_value  order_str    name
3        2        2                         1#2#1#1#1    second
2        2        3                         1#3#1#1#1    first

kindly suggest the needs..

Thanks
Meeran03
0
 
Om PrakashConnect With a Mentor Commented:
Try:
select id,[type], left(right(order_str, len([order_str]) - ([type]*2 - 2 )),1 ) as required_value,order_str,name   from tbl1
where type = 2
order by required_value desc 

Open in new window

0
 
Rajar AhmedConsultantAuthor Commented:
Hi om_prakash_p,

If order_str  got all single digits . its working gud i.e below

Few more data's For correct work,
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(6,'4','fourtype3','1#3#2#3#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(7,'4','fourtype4','1#3#2#4#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(8,'5','fivetype6','1#3#2#1#6')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(9,'5','fivetype5','1#3#2#2#5')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(10,'1','onetype7','7#3#2#1#1')

select id,[type], left(right(order_str, len([order_str]) - ([type]*2 - 2 )),1 )
as required_value,order_str,name   from tbl1
where type = 5
order by required_value


Correct Ok
4    5    5    1#3#2#2#5    fivetype5
3    5    6    1#3#2#1#6    fivetype6
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
But if i had 2 or more  digits in the order_str i get like this,
------------------------------------------------------------
Getting Wrong details in following details
------------------------------------------------------------
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(11,'4','fourtype3','1#30#2#30#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(12,'4','fourtype4','1#200#2#40#1')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(13,'5','fivetype6','1#3#2#1#6')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(14,'5','fivetype5','1#2#2#2#5')
INSERT INTO [tbl1]([id],[type],[name],[order_str])VALUES(15,'1','onetype7','7#3#25#10#1')

select id,[type], left(right(order_str, len([order_str]) - ([type]*2 - 2 )),1 )
as required_value,order_str,name   from tbl1
where type = 4
order by required_value

Output is ::
id    type    required_value    order_str      name
1    4            #                   1#30#2#30#1   fourtype3    (This is getting wrong Data's)
2    4            2                   1#200#2#40#1  fourtype4   (This is getting wrong Data's)
1    4            3                   1#3#2#3#1      fourtype3      (This fine since single digits)
2    4            4                   1#3#2#4#1      fourtype4      (This fine since single digits)

Meeran 03 :)
0
 
Om PrakashConnect With a Mentor Commented:
In this case you may need to use a temp table and cursor to manually process records and store in temp table and do the Order By.
0
All Courses

From novice to tech pro — start learning today.