CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Sharpeye500
Sharpeye500

Reputation: 9063

Searching with spaces in SQL server

In a table i have an entry

12-345678
123-456-789
123456789

In the UI textbox, when someone types:   - (two spaces then one dash).

output should be:

12-345678

In the UI textbox, when someone types: 12-

output should be:

12-345678

In the UI textbox, when someone types: 12

12-345678
123-456-789
123456789

My select query to do this is 

select column1,column2 from table1 where column1 like '%textbox1.text%';

textbox1.text-> textbox name of the UI.

Anything before - needs to be returned, for ex: 

if you type 12-, return output should be 12-345678 alone,
if you type 123-, then the return output should be 123-456-789 alone.
if you type - (only dash, no space on the front , then the return output should be 12-345678 and 123-456-789 .

However it fails in few conditions, is there any way i can calculate the space and modify the query directly in sql?

Upvotes: 0

Views: 4515

Answers (2)

egrunin
egrunin

Reputation: 25053

If I understand this correctly: When there are leading spaces, you need to transform them into _. For cases where there are no leading spaces, you can just append %.

<space><space>-  '__-%'
12-              '12-%'
12               '12%'
123-             '123-%'

But this one doesn't fit the pattern, so it's a special case:

-                '%-%'

I suspect you want a one-line fix, but although you could probably pack it all into a single iff(), I would avoid that if possible.

Edited to add

C#:

string arg = theTextbox.Text;
arg = arg.Replace("'", "''"); // avoid SQL injection attack

arg = arg.Replace(" ", "_"); // space = single character wildcard = '_'

if (arg.StartsWith("-")) // special case
    arg = "%-";

string sqlStatement = string.Format("SELECT column1, column2 " +
             "FROM table1 WHERE column1 like '{0}%', arg);

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Something like 

"Select column1,column2 From Table1 Where column like " + textbox1.text.Replace(' ','_') + "%"

'cept you should use a parameterised query of course.

sql wild card chars are

% 0 to n of any character
underscore 1 single character
[xyz] any single character that is x, y or z

so in your example it wuld be Like '__-%' a space folowed by 2 would be Like '_2%' and pick up all three of your examples as well, as each one is a character followed by "2" followed by someother stuff

Upvotes: 2

Related Questions