Hi

I am trying to sort alphanumeric varchar in a column.

Elsewhere PATINDEX is used for sql, but the function is not available in Report Builder.

Any ideas to create an expression would be appreciated.

Regards

Please elaborate on your question. You can include an extra column to properly sort into the output of your select statement in the DataSet.

Take a look at the "Mid" & "InStr" functions in SSRS.

Yes , there is a connect for the subject 

https://connect.microsoft.com/SQLServer/feedback/details/789996/in-ssrs-what-is-the-equivalent-of-patindex-in-tsql

=Mid(Fields!name.Value, InStr(Fields!name.Value,"_")+1, (InStr(Fields!name.Value, "-") - InStr(Fields!name.Value, "_") - 1))

Naomi thanks for the reply. I want to create a new calculated field and sort on that column.

My problem is that I cannot figure out what expression to use to populate that column from the existing alphanumeric (varchar) column.

 Regards

Can you please post sample of your data and how would you want to sort them?

My suggestion to is to include that computed column in the query itself where you can use patindex and all other necessary functions.

Hi Naomi 

My unsorted list :

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

The resulting sorted list should be :

1

1-ANW

1B-WEL2

2

2-KAN

3A-SIL

3B

3-WEL1

4

4-DIE H

4-MOS2

14B-ANW

30-WEL1

B1A

B2C

B4A

B4B

C2B

CH1

D1-WEL1

E1

E7

GORD-8

P25/23

For the life of me I cannot figure out the expression. Also it is not necessary to create a new column.

I only need to sort in a more "intuitive" manner.

Regards.

It is a bit tricky, here it T-SQL solution:

declare @t table (unSorted varchar(30))
insert into @t (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')

;with cte as (select unSorted, substring(unSorted, 1, patindex('%[^[0-9]%', unSorted  + 'A')-1) as Number,
substring(unSorted, patindex('%[^[0-9]%', unSorted), len(unSorted)) as Alpha
from @t)

select unSorted from cte
order by case when Number = '' then 1 else 0 end, 
cast(Number as int), Alpha

Here it is with the expression I came up with (not sure if it's possible to reproduce in SSRS):

;with cte as (select unSorted, 
case when patindex('[0-9]%', unSorted) = 0 then '9999' else right('0000' + substring(unSorted, 1, patindex('%[^[0-9]%', unSorted  + 'A')-1),4) + substring(unSorted, patindex('%[^[0-9]%', unSorted), len(unSorted)) end as SortExpression
from @t)

select unSorted from cte
order by SortExpression

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