0

Data in the DB: Various users are putting data into my application (as a string from different areas), this data is stored in [SerialID#] DB column. Contents are for e.g. barcode numbers, serial numbers etc.

Edit: Datasearched, Later another user returns and searches for the same product, but enter his search sub/string with/out the chars.

While doing my search user entered string against the [SerialID#] DB column, I need the alphanumeric content to match. (by stripping out the additional chars in between in both the DB/target and the user string/source).

I have to do this because, some vendors A's serial is different from Vendor B's serial I am unable to control how the user puts data into my app.

I need help in searching the string for only the following two scenarios both in SQL server and on the .NET serverside.

For e.g B ABCLKSAL1930239923-938 2998/84 == Match ABClksAL1930239923938299884

Given a source and target string like a barcode/serial with dashes/hyphen;

  1. How can I match the contents alone, ignoring the hypens, slashes, spaces - / \ or ?
  2. How can I match numbers only? (i.e. digits only)
  3. How can I match the contents after the 3 characters BMS8937-932093 == 8937932093
  4. would be nice if this can this be parametrized to a function where it can be reused, [with passing, a) number of first or last characters to ignore b) match digits only vs alphanumberic only c) specificy the length to truncate]

I tried this [A-Za-z0-9] but was not able to figure out how to match the contents per 1, 2 and 3.

everest
  • 103
  • 9
  • 1
    Check this answer http://stackoverflow.com/a/31693412/1080354 to implement regular expressions support in `T-SQL` using `.net`. Basically, you can create such function in `.net` and use it both in `C#` and `T-SQL` code. As there is not build-in support for regex in `T-SQL` I believe this is the way to go. Let me know if you have any issues. – gotqn Dec 02 '15 at 07:16
  • 1
    A regex can't match discontinuous text, you might consider a kind of a replace function. Still, to use a real regex, not wildcard patterns, in TSQL, you'd need a UDF. – Wiktor Stribiżew Dec 02 '15 at 07:59
  • 1
    I don't suppose it's a option to change your app to write two values (in two separate columns): the original string entered, and a second string matching 1. above? – Christopher Stevenson Dec 02 '15 at 08:22
  • I am sorry @stribizhev it is only one string, I should really clarify my question more, after I re read it this morning. – everest Dec 02 '15 at 17:58
  • @ChristopherStevenson sorry I am not sure I fully understand your suggestion. The second value is already in the DB, user is typing to search. Now, if I tried to do a separate column per serial by brand I would end up with too many. – everest Dec 02 '15 at 17:59
  • @gotqn thats a really good sample, i am going to try an build on that, which is faster the CLR ver or regex? – everest Dec 02 '15 at 18:10
  • 1
    @everest The idea is to use .net code in T-SQL statements. In the example, I am creating regexreplace function, but you can check the links for more examples. You can create your own functions on .net and deploy them to the SQL Server instance. – gotqn Dec 02 '15 at 19:33
  • 1
    @everest I was suggesting having a stored version of the contents in the database that is a 'stripped' form, as a pre-processing step (I suppose you could do this with a computed column), and then using the guaranteed simplified column in your compares. – Christopher Stevenson Dec 02 '15 at 21:51
  • 1
    both suggestions work, how can I mark them as answers? – everest Dec 02 '15 at 22:12

2 Answers2

0

Check this answer stackoverflow.com/a/31693412/1080354 to implement regular expressions support in T-SQL using .net. Basically, you can create such function in .net and use it both in C# and T-SQL code. As there is not build-in support for regex in T-SQL I believe this is the way to go. Let me know if you have any issues.
The idea is to use .net code in T-SQL statements. In the example, I am creating regexreplace function, but you can check the links for more examples. You can create your own functions on .net and deploy them to the SQL Server instance. – gotqn

Armali
  • 18,255
  • 14
  • 57
  • 171
0

I don't suppose it's a option to change your app to write two values (in two separate columns): the original string entered, and a second string matching 1. above?
I was suggesting having a stored version of the contents in the database that is a 'stripped' form, as a pre-processing step (I suppose you could do this with a computed column), and then using the guaranteed simplified column in your compares. – Christopher Stevenson

Armali
  • 18,255
  • 14
  • 57
  • 171