3

In my database application I'm intending to give the users simple glob pattern search possibility besides standard search of text literal. (Let's not mention other search options to keep this question simple.)

They will be able to use wildcards *, ? and [...] for character matching. Jo*n will match John, Jon or Johann. Such an entered pattern will be converted to LIKE for T-SQL queries, to RLIKE in MariaDB queries and to VB.NET Like or C# LikeString() invocation for in-memory search.

Is it OK to lead users to get familiar with wildcard characters * and ? if these (used in SQL-89) were abandoned since SQL-92 standard in favor of % and _?.

Honestly, I think that for non-advanced users, * and ? suit better than % and _ because:

  • users know them from OS file masks
  • they appear in data less often than % and _, so there is lesser need to deal with escaping them
  • they are better indicating their wildcard nature than % and _ which are a bit ambiguous (50% can confuse inexperienced users to think it is a literal)
  • they are somewhat easier to type because * is directly available at key * on numeric pad, while all others need pressing Shift.

Are there any real reasons why I should favorize % and _? If SQL-92 switched to them, perhaps they had some reasons.

Improve this question
7
  • When you say they were abandoned, do you mean it's now invalid, or does it still function (properly) but people just don't like using it?
    – UXerUIer
    Feb 3, 2015 at 13:59
  • @Majo0od - it means it is now invalid. * and ? are now treated as regular characters except of some non-standard database engines like Jet which powers Microsoft Access.
    – miroxlav
    Feb 3, 2015 at 14:52
  • Then leading them to use it shouldn't be allowed. You shouldn't create a learned behavior that gets the user no where.
    – UXerUIer
    Feb 3, 2015 at 16:12
  • I'm afraid you didn't get the point. During searching, users are normally not dealing with SQL as you are not dealing with Google internals during searching. So here I'm addressing the surface, not implementation deep inside.
    – miroxlav
    Feb 3, 2015 at 16:15
  • I'm sorry, I thought you meant when it comes to search, that if they should use * or ? while searching. If those options aren't going to do anything, then why should those be used?
    – UXerUIer
    Feb 3, 2015 at 16:19

4 Answers 4

Reset to default
4

What is your interface?

Are users entering SQL SELECT statements directly? If so, you should use % and _ because they're part of the SQL standard, and the user is expecting standard SQL.

Are users entering search terms into an application, which is incidentally using an SQL database as its search backend? In this case, use * and ? as your wildcards, because that's what they're used to from other applications.

Improve this answer
1
  • I agree with you point and moreover, I think that most of users of SQL are at technical level where they already have some experience with glob wildcards, because path to programming including SQL typically goes through other computer basics first.
    – miroxlav
    Feb 5, 2015 at 3:23
4

Are your users more familiar with SQL syntax or search engine syntax? If there's a strong leaning one way or the other, there's your answer.

Improve this answer
4
  • Minimum of users is familiar with SQL syntax. Most of users are either standard office folks, some are managers, then designers, engineers, or even workers using their PC's at production lines. It is small ERP actually.
    – miroxlav
    Feb 3, 2015 at 22:47
  • 1
    @miroxlav well, I'd say there's your answer then. Don't worry about the SQL syntax at all. It's not relevant to this user base.
    – DA01
    Feb 3, 2015 at 23:10
  • Thank you very much for leading me to the answer. I wish I could mark two answers! At least I'm giving you credit and upvote. :)
    – miroxlav
    Feb 5, 2015 at 3:25
  • @miroxlav glad it helped!
    – DA01
    Feb 5, 2015 at 4:29
3

As for why SQL changed?
This is just my guess 

select *  
  from table

The * means all columns and they did not want to use * for two things

Second I think you would search for a literal * more often than %

Let's say you pick % for the wildcard
If they enter a * then display a warning message
Reminder in productname % is the wildcard - not *
or if the enter ?
Reminder in productname _ is the wildcard - not ?

If you are going to pick * ? then just reverse the reminders

If you are going to get really fancy color up the wild cards as they enter them

I have product that went into a new business space and that space was used to * ? and the product used % _. Rather than split the product we went with the warning messages,

Improve this answer
4
  • They already use * for two things: it's also used for multiplication. Like, I believe even in standard SQL, % is also a remainder operator. (It definitely is in MS T-SQL, which is the dialect I work with the most.)
    – user
    Feb 4, 2015 at 17:42
  • @MichaelKjörling OK then they want to use * for less things. Either way need to deal with there does not appear to single standard here.
    – paparazzo
    Feb 4, 2015 at 17:46
  • Thank you for the answering the secondary question. After posting it, I tried to post it as standalone question on programmers SE.
    – miroxlav
    Feb 5, 2015 at 3:28
  • Come on down vote. Why?
    – paparazzo
    Feb 5, 2015 at 17:41
1

Some general computer-literate folks would assume * and ? and database-literate folks would assume % and _ and the general public would assume the computer is smart enough to read your mind and that you don't need a trailing wildcard to match.

So, here is where on-screen help text (always visible) is needed.

Last Name _________    (Use % to match zero or more characters, _ to match one character)
                       (Sm%th matches Smth, Smyth, Smith but not Smythe)
Improve this answer
1
  • Good point, hints should never be at last place! There is not much screen estate left in our case, but perhaps field can be decorated with something like different background color or blue corner. Help can pop-up until users mark [x] do not show me again etc...
    – miroxlav
    Feb 5, 2015 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.