I have a masked textbox in c# winform.
The masking would be like
- (two numbers on the front and 4 number on the right after the dash).
for ex: 12-3456
There are more permutations like
- (3 spaces on the front and 5 on the right)
for ex: 123-34567
When a user types 123-34567, the select sql query should return only
123-34567
When a user types 12-3456, the select sql query should return only
12-3456
When a user types -, the select sql query should return only
12-3456 (ie. two spaces typed)
When a user types -, the select sql query should return only
123-34567 (ie. three spaces typed)
In other words, a user can search without entering anything and have only mask enabled text box and search(empty - empty) - only dash mask , type number(s) on the mask and search (for ex: 12- ).
The query am using is
select column1,column2 from table1 where column2 like '%__-%';
(the underscores are dymanically calculated) and how do i get this in any other optimum approach (say like in a single query) ?
Consider this table1 and having a column "MaskedInfo" in the database.
Table1:
MaskedInfo
1234567
12-34567
123-4567
123-45678
User can type anything to search like 12-34567 or 123-4567 or simply 1234567 and if the text box is empy load everything in the result.
The problem seems to be the first %, you're changing the spaces int he user input for underscores, there you're right but the first % will get you results with something before the underscores, so
like '%__-%' //(two underscores)
will get anything that has two or more digits before the -,
like '%___-%' //(three underscores)
will get anything that has three or more digits before the -
removing the first % will get the result that have exactly as many digits as underscores