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.

Improve this question
10
  • sounds like you want a combination of this: stackoverflow.com/questions/10443462/… and this: stackoverflow.com/questions/11808573/sql-order-string-as-number/… Commented Apr 17, 2021 at 0: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. Commented Apr 17, 2021 at 2: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 Commented Apr 17, 2021 at 8: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 Commented Apr 17, 2021 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...(?) Commented Apr 17, 2021 at 12:01
Related questions
5
want a Query to make order by variable in Linq query
3
ADO.NET Entity Framework: Converting String to Int in Where/OrderBY
2
Linq to SQL - How to order numbers as strings?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.