locked
LINQ to SQL - split string field by space character? RRS feed

  • Question

  • User-745958333 posted

    Hi.

    Is there any way to split a string by the space character in Linq to SQL?  I have a postcode returned as a field, for example it may be 'L22 51J'.  And I want to get the first part and the second part of the postcode.  I've tried:

    postcode1 = p.PropertyZipOrPostcode.Substring(0, p.PropertyZipOrPostcode.IndexOf(' ')),
    postcode2 = p.PropertyZipOrPostcode.Substring(p.PropertyZipOrPostcode.Length - p.PropertyZipOrPostcode.IndexOf(' ')),

    and also

    postcode1 = p.PropertyZipOrPostcode.Split(' ')[0]
    postcode2 = p.PropertyZipOrPostcode.Split(' ')[1]

    But Linq to SQL doesn't support Arrays or IndexOf it seems....

    Thanks!

     postcode_1 = p.PropertyZipOrPostcode.Substring(0, p.PropertyZipOrPostcode.IndexOf(' ')),

    Tuesday, January 19, 2016 8:50 PM

Answers

  • User-745958333 posted

    Sorted!  The solution is to use this, ensuring it is 

    System.Data.Entity.SqlServer

    .and not

    System.Data.Objects.SqlClient

    and then we can do this:

    postcode1 = p.PropertyZipOrPostcode.Substring(0, (SqlFunctions.CharIndex(" ",p.PropertyZipOrPostcode).Value - 1)),
    postcode2 = p.PropertyZipOrPostcode.Substring(p.PropertyZipOrPostcode.Length - (SqlFunctions.CharIndex(" ", p.PropertyZipOrPostcode).Value - 1)),

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2016 9:10 AM

All replies

  • User281315223 posted

    I don't know if this is an option for you, but you could make this into an in-memory query, which would allow you to use all of power of .NET as opposed to being limited to just those calls supported by SQL. You would just need to include an AsEnumerable() or ToList() call prior to performing your logic :

    var output = YourCollection.ToList()
                               .Select(p => new {
                                    postcode1 = p.PropertyZipOrPostcode.Split(' ')[0]
                                    postcode2 = p.PropertyZipOrPostcode.Split(' ')[1]
                                });

    Tuesday, January 19, 2016 9:31 PM
  • User753101303 posted

    Hi,

    Captain Planet

    Linq to SQL doesn't support Arrays or IndexOf it seems

    Keep in mind that your query is translated to SQL so it is possible with basic operation but doing something too complex (such as creating an addressing arrays) won't work (or else be explicit about what happens to avoid any interpretation).

    I would likely just retrieve the data client side first and then would expose that (maybe as additional read only properties).

    Tuesday, January 19, 2016 9:41 PM
  • User-745958333 posted

    Sorted!  The solution is to use this, ensuring it is 

    System.Data.Entity.SqlServer

    .and not

    System.Data.Objects.SqlClient

    and then we can do this:

    postcode1 = p.PropertyZipOrPostcode.Substring(0, (SqlFunctions.CharIndex(" ",p.PropertyZipOrPostcode).Value - 1)),
    postcode2 = p.PropertyZipOrPostcode.Substring(p.PropertyZipOrPostcode.Length - (SqlFunctions.CharIndex(" ", p.PropertyZipOrPostcode).Value - 1)),

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2016 9:10 AM