Search string without special characters

Question

Search string without special characters

Boris Pazin

Ten Centuries

Points: 1036
More actions
August 15, 2013 at 1:22 am

#296994

Hello,
I am trying to update our search engine. There is a field called product SKU. Each product has its own sku and it's vachar(100) field.
Currently, this field is searched as exact term with simple:
WHERE product_sku = @searchTerm
But, the problem is that customers sometimes don't know exact sku.
For example, for sku = 'F-120-SB', someone tries just F120 (without dash).
For sku F-120-SB, product should be found for these search terms:
F-120-SB (exact term)
F 120 SB (this is done easily with full text search)
F120SB (not sure how to get this)
F120 (just part and without characters, have no idea how to achieve this 🙂 )
In short, I have to search part of string without certain characters like -,<>"
The best idea I have now, is to simply create another field called sku_variations and put there all variations, in this case it would be sku_variations = 'F120SB F120' and then simply search one more field.
Any better idea?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

That's a nice problem you have on your hands.

My first guess would be to create an additional (calculated) column and put in the value without all special characters (F120SB). Also remove all special characters from the entered search string by the user and add an '%' because it can only be the first part. So the entered value "F_120" will be changed to "F120%". Add an additional search clause (or replace the existing search) that searches for the altered search string with a LIKE statement on the additional column.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

jethrow SSC Rookie Points: 49 More actions · Answer 2

RegEx would be nice in T-SQL ... but here's an an example implementing what HanShi recommended:

CREATE FUNCTION AlphaNum (@s varchar(100))

RETURNS varchar(100) AS

BEGIN

Declare @n int = PatIndex('%[^A-Z0-9]%', @s-2);

While @n <> 0 Begin

Select@s-2 = Stuff(@s, @n, 1, ''),

@n = PatIndex('%[^A-Z0-9]%', @s-2) End;

Return @s-2;

END

GO

Declare @search varchar(10) = 'F120';

With Products (SKU, Name) As (

Select 'F-120-SB', 'MyProduct'

)

SELECT*

FROMProducts

WHERESKU = @search

ORdbo.AlphaNum(SKU) LIKE '%'+dbo.AlphaNum(@search)+'%'

Drop Function AlphaNum;

SQL Server Noobie

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 3

jethrow (8/18/2013)
RegEx would be nice in T-SQL ... but here's an an example implementing what HanShi recommended:
CREATE FUNCTION AlphaNum (@s varchar(100))
RETURNS varchar(100) AS
BEGIN
Declare @n int = PatIndex('%[^A-Z0-9]%', @s-2);
While @n <> 0 Begin
Select@s-2 = Stuff(@s, @n, 1, ''),
@n = PatIndex('%[^A-Z0-9]%', @s-2) End;
Return @s-2;
END
GO
Declare @search varchar(10) = 'F120';
With Products (SKU, Name) As (
Select 'F-120-SB', 'MyProduct'
)
SELECT*
FROMProducts
WHERESKU = @search
ORdbo.AlphaNum(SKU) LIKE '%'+dbo.AlphaNum(@search)+'%'
Drop Function AlphaNum;

It is definitely pretty function, but I am afraid that using regex function in search stored procedure could affect performances?

Anyway, I resolved this problem already from the other side. When product is created, custom function creates all versions of SKU which are then stored in other table column. When search is performed, procedure is looking to two fields (original SKU and SKU variations).