Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Cancerion
Starting Member
2 Posts |
Posted - 2013-02-14 : 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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-02-14 : 09:34:38
|
where replace(replace(replace(fld,'-',''),'/',''),'/','') = '123456789'Also seehttp://www.nigelrivett.net/SQLTsql/RemoveNonNumericCharacters.htmlFor 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 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 YourPartsTableWHERE @searchString = REPLACE(REPLACE(REPLACE(part_number,'/',''),'-',''),'\','') Editing: nigelrivett was faster than me. How do you insert that picture of the buffalo being shot? [InsertPictureOfBuffaloBeingShotHere] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-14 : 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 YourPartsTableWHERE @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 MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 10:24:03
|
Aha! Thanks, Visakh :) |
|
|
Cancerion
Starting Member
2 Posts |
Posted - 2013-02-14 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 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 YourPartsTableWHERE REPLACE(REPLACE(REPLACE(@searchString,'/',''),'-',''),'\','') = REPLACE(REPLACE(REPLACE(part_number,'/',''),'-',''),'\','') |
|
|
|
|
|
|
|