0

How to extract numbers from string(nvarchar) in LINQ Query.

For example: SEQ01, I want to take only the number (01) and execute order by on it. The problem with default string order by is that it orders it 1, 10, 100, 2, 3 etc..

I have tried the multiple methods to handle this, TryParse, Convert.ToInt32 does not work since it has part which is not a number. Sorting first by length than by the string still wont work in my case since the string part (SEQ) can be changed and has variable length.

We are using .NET Core 3.1 so SqlFunctions are not present.

And the ordering must be on database side, ordering in memory is not performant enough for us.

Viktor
  • 1
  • sounds like you want a combination of this: https://stackoverflow.com/questions/10443462/how-to-get-the-numeric-part-from-a-string-using-t-sql and this: https://stackoverflow.com/questions/11808573/sql-order-string-as-number/11808592 – Rufus L Apr 17 '21 at 00:08
  • While you can convert it to a numeric value on the fly database-side, this could get expensive in terms of resources on your database server. You say that doing the ordering client-side (in memory) is not performing well, which implies that you are working with a large dataset. If this is the case, you're just moving the heavy work from one machine to another. A better option may be to add a new int/numeric column to your table and store the sequence number there, and include it in one of the indexes used by your query. – KristoferA Apr 17 '21 at 02:28
  • You're trying to "make a silk purse out of a pig's ear" as we say; you've reached the point where you need to model your data differently – Caius Jard Apr 17 '21 at 08:36
  • @KristoferA and CaiusJard, that was my initial solution, creating new column for the sequence number. The simple DB query that works is casting the string to an int, but the problem here is that there is no way to do the casting in the LINQ Query. RufusL – Viktor Apr 17 '21 at 10:59
  • If you do the casting in your query, then: 1) all rows you are reading need to have those values converted every time the query is executed, and 2) no indexes on that column will be used. If this is a small table, or if the records you are retrieving are limited then that should not be a concern. However, since you said that a client-side in-memory sort is too expensive, it sounds like you are dealing with a large number of rows/records...(?) – KristoferA Apr 17 '21 at 12:01
  • Yes, the table I do the sorting on is pretty large and also has a large amount of columns as well, that is why I wanted to avoid adding new column specifically for the sorting, but I guess there is no better way of doing it. – Viktor Apr 17 '21 at 12:48
  • Does a large amount of the data in it change frequently? (Many inserts/updates/deletes?) If not, one option could be an indexed view that contains just the columns you need, including the sort column in numeric format. That way the table itself stays intact but you have what is essentially a table of its own for your sort query. Of course, that comes at a cost of additional disk space usage and impacts insert/update/delete performance in a negative way. It all depends on how you use it... – KristoferA Apr 18 '21 at 03:00
  • If you can provide more details about the table, your query, how it will be used, that will make it easier to make more specific recommendations. – KristoferA Apr 18 '21 at 03:04
  • The data changes all the time (this is pretty much the core table of the app), the query gets used after every single filter change and sort change (it has all the filters you can imagine, or/and scroll the table). – Viktor Apr 18 '21 at 11:28
  • Ok. I'd go for a separate sort column. It can be a computed column that you can also include in the relevant indexes to make it all more efficient. – KristoferA Apr 19 '21 at 08:53

0 Answers0