asp.net consultancy chorley ASP.NET Server Side Scripting Wigan
Website Development Radcliffe

IT Services And Support

Email : ren@techsolus.co.uk
Mobile Phone : 0788 68 41 411
Answerphone : 01204 469683
bespoke invoice systems Standish Information Technology Advice Worsley
    development systems Ramsbottom
data manipulation Farnworth access databases Whitefield SQL connections Atherton
online accessible software Leyland software compatible Coppull


Get connected and Online Tottington
remote management Makerfield code and design Aspull

Access Numeric Wildcard Weirdness - 12/10/2011

I use Access databases for some of my customers.  Oh yeah MySQL and MSSQL services are so much more powerful and stable and all that sort of nonsense, but most of my customers are small businesses with small database requirements.  The beauty of an Access database is that it's small, compact, transferable and super easy to look after.  I go to a lot of businesses with 2 or 3 office staff and their systems are seriously overspecified with Active Directory servers and MSSQL running next-to-nothing on the server.

Access is great then for small systems, but it's not without its quircks.  In this instance it's putting together nice clean SQL for queries.

I can create a query IN Access to search numerical values.  So I've a field containing numbers and I want to search all records for those numbers starting with "123".  The SQL would then be...

SELECT * FROM mytable WHERE numericalfeld LIKE '123*'

Simples!  Yep, until you try to use the same SQL to access the database from a ASP.Net web page.  It seems that because I use / ASP.Net uses "Jet" to connect to the database then there's a difference in the wildcards.  AND and and...the "Jet" method doesn't support numerical wildcards!  Dagnammit, how annoying and frustrating as that.  So how the deuce am I supposed to collect all the numbers beginning with "123" from my table?  There is no simple solution.  I would love to find a nice simple wildcard that does the same sweet job as the asterisk (*), but according to a handful of websites there is no numerical wildcard in Jet. 

Jet however does manage to support a sort of regex list thingy.  To select everything starting with "123" and has only one more character you can use...

SELECT * FROM mytable WHERE numericalfeld LIKE '123{1234567890]'

So this would select 1234, 1235, 1236 but NOT 12341 or 12342.  So to select numbers with, in this instance, 2 wildcard characters...

SELECT * FROM mytable WHERE numericalfeld LIKE '123{1234567890][1234567890]'

So now you would see 12341, 12342, 12343 but NOT 1234 1235 OR 123456, 123457

So, in  my instance there was a possibility of 4, 5 or 6 character numbers.  I used  the OR statement to fix this...

SELECT * FROM myable WHERE numericalfield LIKE
     123[1234567890]
      OR
     123[1234567890][1234567890]
     OR
     123[1234567890][1234567890][1234567890]

It's long winded and codey.  It does however work just fine.  IF anyone knows a better way I'm open to suggestions...use the comments box below.

Post A Comment

Name Comment
programming services asp.net specialist
Valid XHTML 1.0 Transitional
Admin
GD