Search code examples
stringdevexpresssubstringstring-matching

Get Substring without Regular Expression

This is going to be a very long question so please bear with me (also please help me as I'm at my wit's end) but let me give you the context of what I'm doing first.

I have a strain that is a result of concatenating different stuff (it ends up being a long string). An example would be:

C1;371 (Huz-fer)/GT(BLEA)56Mar<sup>nq5(LAG-HQRZ3*,-tMoline)KTA</sup> (iBet-huz/W-HEAPP) [stock 12345] [pregnant]

Let me break down the structure of the string.

Formal Name

C1;371 (Huz-fer)/GT(BLEA)56Mar<sup>nq5(LAG-HQRZ3*,-tMoline)KTA</sup>

This will always be at the beginning of the string and could be a long name which may also include strings enclosed in ( and ) within it.

Model Name

(iBet-huz/W-HEAPP)

This is what I need to get and extract - it will always immediately follow after the formal name and will always be enclosed in ( and ). It could also potentially contain substrings enclosed in another set of ( and ) - so something like (iBet-huz/W-(vuts)HEAPP) is completely possible.

Stock

[stock 12345]

This will always be enclosed in [ and ] and start with the word stock.

Animal Type

[pregnant]

This will always be at the end of the string enclosed in [ and ].

In the example above I need to get the model name (iBet-huz/W-HEAPP) or in the other case mentioned (iBet-huz/W-(vuts)HEAPP). In Oracle SQL I could easily do this by using regular expressions:

SELECT DISTINCT REGEXP_SUBSTR(ss.strain_value, '\(.*?\)', 1, REGEXP_COUNT(ss.strain_value, '\(.*?\)')) AS name
FROM species_strain ss
WHERE INSTR(ss.strain_value, '(', 1, 1) > 0
ORDER BY name ASC

But my problem now is I need to do the same thing in a third-party software that stripped down the available functions I can use in Oracle (REGEXP_SUBSTR and REGEXP_COUNT) are among those removed which means I can't do any string manipulations while querying within that software. Luckily they provided some tools outside of querying for string manipulations (albeit not as extensive).

Available String Manipulation Functions

  • Ascii(String) - Returns the ASCII code value of the leftmost character in a character expression.
  • Char(Number) - Converts an integerASCIICode to a character.
  • CharIndex(String1, String2) - Returns the starting position of String1 within String2, beginning from the zero character position to the end of a string.
  • CharIndex(String1, String2, StartLocation) - Returns the starting position of String1 within String2, beginning from the StartLocation character position to the end of a string.
  • Concat(String1, ... , StringN) - Returns a string value containing the concatenation of the current string with any additional strings.
  • Contains(String, SubString) - Returns True if SubString occurs within String; otherwise, False is returned.
  • EndsWith(String, EndString) - Returns True if the end of String matches EndString; otherwise, False is returned.
  • Insert(String1, StartPosition, String2) - Inserts String2 into String1 at the position specified by StartPositon.
  • Len(Value) - Returns an integer containing either the number of characters in a string or the nominal number of bytes required to store a variable.
  • Lower(String) - Returns the String in lowercase.
  • PadLeft(String, Length) - Left-aligns characters in the defined string, padding its left side with white space characters up to a specified total length.
  • PadLeft(String, Length, Char) - Left-aligns characters in the defined string, padding its left side with the specified Char up to a specified total length.
  • PadRight(String, Length) - Right-aligns characters in the defined string, padding its left side with white space characters up to a specified total length.
  • PadRight(String, Length, Char) - Right-aligns characters in the defined string, padding its left side with the specified Char up to a specified total length.
  • Remove(String, StartPosition, Length) - Deletes a specified number of characters from this instance, beginning at a specified position.
  • Replace(String, SubString2, String3) - Returns a copy of String1, in which SubString2 has been replaced with String3.
  • Reverse(String) - Reverses the order of elements within a string.
  • StartsWith(String, StartString) - Returns True if the beginning of String matches StartString; otherwise, False is returned.
  • Substring(String, StartPosition, Length) - Retrieves a substring from String. The substring starts at StartPosition and has the specified Length.
  • Substring(String, StartPosition) - Retrieves a substring from String. The substring starts at StartPosition.
  • ToStr(Value) - Returns a string representation of an object.
  • Trim(String) - Removes all leading and trailing SPACE characters from String.
  • Upper(String) - Returns String in uppercase.

With those available functions above I was able to come up with a solution to the first case where the model name was (iBet-huz/W-HEAPP) but not for the next case where the model was (iBet-huz/W-(vuts)HEAPP).

MY CURRENT SOLUTION (does not solve the second case)

In the third-party software we are using you can create a variable and use the functions enumerated above.

--------------------------------------------------------------------------
[reversed_strain]
--------------------------------------------------------------------------
Trim(Reverse([STRAIN_VALUE]))

--------------------------------------------------------------------------
[removed_animal_type]
--------------------------------------------------------------------------
Iif(
    StartsWith([reversed_strain], ']'),
    Trim(Remove([reversed_strain], 0, CharIndex('[', [reversed_strain]) + 2)),
    [reversed_strain]
)

--------------------------------------------------------------------------
[model_name]
--------------------------------------------------------------------------
Iif(
    StartsWith([removed_animal_type], ')'),
    Trim(Reverse(Substring([removed_animal_type], 1, CharIndex('(', [removed_animal_type]) - 1))),
    [STRAIN_VALUE]
)

The basic idea behind my approach:

  1. Reverse the entire string to put the animal type, stock, and the model name towards the beginning of the string.
  2. Remove the animal type - which puts the stock at the beginning of the string.
  3. Remove the stock - which puts the model name at the beginning of the string.
  4. Get the string from the beginning up to the first ( - the reason why I'm looking for the opening bracket is because I reversed the string.

Could you help me modify my solution so that I can also take into account the second case where I can have an arbitrary number of ( and ) inside the model name?

I know the question is pretty long already but if you need more information just let me know.

Addendum

My solution doesn't show me removing the stock part of the string but that's fine I could just do the same thing I did with removing the animal type so don't worry about it - just imagine that the stock was already removed.

Solution

  • If your formal name and your model name are separated by space then you can use it to find model name.
    Here is example:

    --------------------------------------------------------------------------
[strain_removed]
--------------------------------------------------------------------------
Substring([strain_value], 0, CharIndex(') [', [strain_value]) + 1)

--------------------------------------------------------------------------
[model_name]
--------------------------------------------------------------------------
Substring([strain_removed], Len([strain_removed]) - CharIndex('( ', Reverse([strain_removed])) - 1)

    You can try this example: 

    var table = new System.Data.DataTable();
table.Columns.Add("strain_value");

table.Rows.Add("C1;371 (Huz-fer)/GT(BLEA)56Mar<sup>nq5(LAG-HQRZ3*,-tMoline)KTA</sup> (iBet-huz/W-HEAPP) [stock 12345] [pregnant]");
table.Rows.Add("C1;371 (Huz-fer)/GT(BLEA)56Mar<sup>nq5(LAG-HQRZ3*,-tMoline)KTA</sup> (iBet-huz/W-(vuts)HEAPP) [stock 12345] [pregnant]");

var labelItem1 = new XRLabel();
labelItem1.DataBindings.Add("Text", null, "model_name");

var detail = new DetailBand();
detail.Controls.AddRange(new XRControl[] { labelItem1 });

var strain_removed = new CalculatedField();
strain_removed.Name = "strain_removed";
strain_removed.Expression = "Substring([strain_value], 0, CharIndex(') [', [strain_value]) + 1)";

var model_name = new CalculatedField();
model_name.Name = "model_name";
model_name.Expression = "Substring([strain_removed], Len([strain_removed]) - CharIndex('( ', Reverse([strain_removed])) - 1)";

var report = new XtraReport();
report.Bands.Add(detail);
report.DataSource = table;
report.CalculatedFields.AddRange(new[] { strain_removed, model_name });

report.ShowRibbonPreview();

    But in XtraReport you can also use scripts or events to do whatever you want in any .net language. For example, you can use BeforePrint event of xrLabel control and GetCurrentColumnValue method to get the current column value:

    using System.Text.RegularExpressions;

private void label1_BeforePrint(object sender, PrintEventArgs System.Drawing.Printing.PrintEventArgs e)
{
    string strain_value = GetCurrentColumnValue("strain_value").ToString();

    string model_name = ... // <= Your regex computations.

    label1.Text = model_name;
}