| Author |
 Topic  |
|
|
Cancerion
Starting Member
2 Posts |
 Posted - 02/14/2013 : 09:09:31
|
SUBJECT: SQL Queries not returning all possible data.
Considering Regular Expressions(?)
I've got a small problem. I built a database for my work to keep track of part numbers for on-call technicians spread across our State.
The problem? There is no 'standard' for the part numbers we carry for all our different manufacturers.
Some have slashes (/, \), dashes (-), and other special characters in our part numbers and they are never in the same index location in the string.
I'm currently using wildcards ('% searchInput %') to search which works great up until the user fails to input that first special character in which case 90% of the returned fields drop out of view.
What i'm attempting to do is search for "123456789"
and return the rows from the database containing:
"1-234-56789"
"1/2345678/9"
"1234567-89"
"123456789"
My Question: Is there any way to allow these characters in the stored data but have the server ignore them for my SELECT queries?
I was considering researching and using Regular Expressions but that would require the special characters to always be in the same location in the part numbers, correct?
My users should know enough to type in the special characters however, in the event they don't and can't find the part they need it could end up costing us a lot in the way of time and money.
A helpful nudge in the right direction would greatly be appreciated. |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 02/14/2013 : 09:34:38
|
where replace(replace(replace(fld,'-',''),'/',''),'/','') = '123456789'
Also see
http://www.nigelrivett.net/SQLTsql/RemoveNonNumericCharacters.html
For removing non-numeric characters from a string - you might want to maintain another column without the special characters.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
James K
Flowing Fount of Yak Knowledge
1508 Posts |
Posted - 02/14/2013 : 09:37:07
|
Unfortunately, T-SQL does not support regular expressions natively. You could use CLR functions that implement regular expressions, for example here: http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/
If you know all the possible special characters, you could use a nested replace to do the comparison. In the example below, I am replacing dashes, forward slashes and backward slashes:SELECT * FROM YourPartsTable
WHERE
@searchString = REPLACE(REPLACE(REPLACE(part_number,'/',''),'-',''),'\','')
Editing: nigelrivett was faster than me. How do you insert that picture of the buffalo being shot? [InsertPictureOfBuffaloBeingShotHere] |
Edited by - James K on 02/14/2013 09:38:33 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 02/14/2013 : 09:51:22
|
quote:
Originally posted by James K
Unfortunately, T-SQL does not support regular expressions natively. You could use CLR functions that implement regular expressions, for example here: http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/
If you know all the possible special characters, you could use a nested replace to do the comparison. In the example below, I am replacing dashes, forward slashes and backward slashes:SELECT * FROM YourPartsTable
WHERE
@searchString = REPLACE(REPLACE(REPLACE(part_number,'/',''),'-',''),'\','')
Editing: nigelrivett was faster than me. How do you insert that picture of the buffalo being shot? [InsertPictureOfBuffaloBeingShotHere]
use sniped within []
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1508 Posts |
Posted - 02/14/2013 : 10:24:03
|
Aha! Thanks, Visakh :)
|
|
|
|
Cancerion
Starting Member
2 Posts |
Posted - 02/14/2013 : 10:41:27
|
Oh my, you guys are great!
I was confusing myself with RegEx tutorials worrying about how much development overhead it's going to add to our Citrix application and all I have to do is edit my SQL queries?!
I haven't dealt with SQL since I was in college 10+ years ago developing VB6.0 and ActionScript applications.. so this whole project has been a huge, but necessary, task of retraining myself from the ground up for VB.Net and MSSQLServer2008.
I sincerely appreciate your help!
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1508 Posts |
Posted - 02/14/2013 : 11:38:08
|
Glad to help!
One other thing, now that I am looking at it a second time, is that you should do the REPLACE on both sides of the WHERE clause expression, to account for cases where the user inputs correctly with the hyphens and dashes and when they do not:SELECT * FROM YourPartsTable
WHERE
REPLACE(REPLACE(REPLACE(@searchString,'/',''),'-',''),'\','')
=
REPLACE(REPLACE(REPLACE(part_number,'/',''),'-',''),'\','')
|
|
|
| |
Topic |
|
|
|
SQL Queries not returning all "possible" data..
