asked on

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

ASKER CERTIFIED SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajar Ahmed

ASKER

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

SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajar Ahmed

ASKER

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 :)

SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial