3

I want to search for a string pattern in a database table.

The search pattern is simply the string entered by the user. However the field to be searched can have spaces in between, or user might enter spaces in the string.

So I want to create a regex, such that if say the string is "Test", I look for regex which might have spaces in between, something like this

T[\s]*e[\s]*s[\s]*t.

Can anyone please suggest how I can do this in Entity framework - or by using ESQL?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
gunnerz
  • 1,898
  • 5
  • 24
  • 39
  • Are spaces the only reason you need to use regex? You could remove all the spaces from the field and search criteria and then compare them. – ckal Apr 24 '13 at 18:08
  • I cannot remove the spaces from the field in the database - because user enters it - and the requirement is to preserve it as it is. – gunnerz Apr 25 '13 at 08:20

1 Answers1

2

For that pattern, you don't need a Regex. You're best off trimming them out, then doing the search. You could so something like:

SELECT * FROM MyTable
WHERE REPLACE(MyColumn, ' ', '') LIKE '%' + REPLACE(@YourVariable, ' ', '') + '%'
Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
  • This only solves half of the problem. It will find record in the database if it doesn't have any spaces. However user might enter a record with spaces - and he can put space after any character. This search will not work then. Thats why regex is needed. – gunnerz Apr 25 '13 at 08:18
  • @gunnerz Then we can take the spaces out of the column as well, so both are an equal comparison. Check my updated edit. I've added a trim for both. – Mathew Thompson Apr 25 '13 at 08:23
  • Thanks this works! I am not sure how efficient the search will be. Also I am guessing the onlyway to use it in entity framework is through ESQL. – gunnerz Apr 25 '13 at 09:03