SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Queries not returning all "possible" data..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cancerion
Starting Member

2 Posts

Posted - 02/14/2013 :  09:09:31  Show Profile  Reply with Quote
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
3383 Posts

Posted - 02/14/2013 :  09:34:38  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/14/2013 :  09:37:07  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/14/2013 :  09:51:22  Show Profile  Reply with Quote
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/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/14/2013 :  10:24:03  Show Profile  Reply with Quote
Aha! Thanks, Visakh :)

Go to Top of Page

Cancerion
Starting Member

2 Posts

Posted - 02/14/2013 :  10:41:27  Show Profile  Reply with Quote
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!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/14/2013 :  11:38:08  Show Profile  Reply with Quote
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,'/',''),'-',''),'\','')
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000