SSRS Report Expression for Sorting AlphaNumerical Field


  

create table test (id int identity, unSorted varchar(30))
insert into test(unSorted)
values ('2-KAN'),('1'),('2'),('4'),('B1A'),('B2C'),('B4A'),('B4B'),('C2B'),('E1'),('E7'),('3-WEL1'),('30-WEL1'),('D1-WEL1'),('1B-WEL2'),
('CH1'),('1-ANW'),('14B-ANW'),('4-MOS2'),('P25/23'),('GORD-8'),('3A-SIL'),('4-DIE H'),('3B')




= CDbl(CStr(IIF(Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value, 1 ) ) <=57 and Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value, 1 ) ) >=48 
,CStr(Getchar(Fields!unSorted.Value, 1) )
+Cstr(  IIF(len(Fields!unSorted.Value)>1,
IIF(Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1)) ) <=57 and Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1)) ) >=48 ,Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1)),""),"") )
 
,
 CDbl(Cstr(Microsoft.VisualBasic.AscW(GetChar(Fields!unSorted.Value,1)))+
  Cstr(IIF(len(Fields!unSorted.Value)>1, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>1,2,1))),0.0))/100
  +
    Cstr(IIF(len(Fields!unSorted.Value)>2, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>2,3,1))),0.0))/1000
	+
    Cstr(IIF(len(Fields!unSorted.Value)>3, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>3,4,1))),0.0))/100000
	+
    Cstr(IIF(len(Fields!unSorted.Value)>4, Microsoft.VisualBasic.AscW(Getchar(Fields!unSorted.Value,IIF(len(Fields!unSorted.Value)>4,5,1))),0.0))/10000000
)))
)



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s