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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Queries not returning all "possible" data..

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 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
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 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]
Go to Top of Page

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 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 10:24:03
Aha! Thanks, Visakh :)

Go to Top of Page

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!
Go to Top of Page

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 YourPartsTable
WHERE
REPLACE(REPLACE(REPLACE(@searchString,'/',''),'-',''),'\','')
=
REPLACE(REPLACE(REPLACE(part_number,'/',''),'-',''),'\','')
Go to Top of Page
   

- Advertisement -