0

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.

Sharpeye500
  • 8,775
  • 25
  • 95
  • 143
  • Do you wanna check the samples in your question? They dont seem to be following your logic. Or mebbe I do not understand the connection between what the user types in the masked textbox and how it is stored in SQL Server! – Kash Aug 16 '12 at 17:26
  • Ah, this textbox is for search! We needed that. – Kash Aug 16 '12 at 17:31

2 Answers2

0

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

saul672
  • 737
  • 5
  • 6
  • Yes, can this be done in more optimum way, say like in a single query? – Sharpeye500 Aug 16 '12 at 17:39
  • The pseudocode will be if two spaces before dash and no numbers or letters, then '%___-%' like this, I am not able to get it in a single query, there is CASE WHEN, but am not getting it in a single select query. – Sharpeye500 Aug 16 '12 at 18:02
  • 1
    maybe you can change the string used in the like for a parameter in the command select column1, column2 from table where column2 like @Param and just pass the value of the textbox as the parameter sqlcommand.Parameters.Add(new SqlParameter("@Param",textbox.text.replace(" ","_")+"-%")); – saul672 Aug 16 '12 at 18:07
0

You can try to use charindex:

WHERE (@search = '' OR charindex('-', @search) = charindex('-', column2))

michaelhofer
  • 11
  • 2