2

Here is the issue - I have a database column that holds product serial number that are filled in by users, but without any kind of filters. For example, the user can fill the field as: DC-538, DC 538 or DC538, depending on his own interpretation - since the serial number is usually in the metal part of the product and it can be difficult to know If there's a blank space for examplo.

I can't format the current column values, because that are so many brands and we couldn't know for sure If taking out a non alpha numeric character can lead to problems. I mean, If they consider these kinds of character as part of an official number. For example: "DC-538-XXX" and "DC538-XXX" could be related to 2 different products. Very unlikely, but we cannot assume it doesn't happen.

Now I need to offer a search by serial number in my website... but, If the user searchs for "DC538" instead of "DC 538" he won't find it. What's the best approach ?

I believe that the perfect solution would be to have a kind of select that would search the exact string and also strip the non-alpha-num from the search term and compare to a stripped string in the database (that I don't have). But I don't know If there's a way to do that with SQL only.

Any ideas ?

Cheers

mEba
  • 23
  • 8
  • You're on the right track and that is the way to do it. Parse the input from the user by removing non-alpha-numeric characters and compare it to the values stored in the column after they have already been passed through the same process. – Radu Gheorghiu Jul 03 '15 at 14:33

1 Answers1

1

By using the below function, which was offered as an answer here and modifying it to return numeric characters:

CREATE FUNCTION [dbo].[RemoveNonAlphaCharacters] (@Temp VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @KeepValues AS VARCHAR(MAX)

    SET @KeepValues = '%[^a-z0-9]%'

    WHILE PatIndex(@KeepValues, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END

You can do the following:

DECLARE Input NVARCHAR(MAX)
SET @Input = '%' + dbo.RemoveNonAlphaCharacters('text inputted by user') + '%'

SELECT *
FROM Table
WHERE dbo.RemoveNonAlphaCharacters(ColumnCode) LIKE @Input

Here is a sample working SQLFiddle

Community
  • 1
  • 1
Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • All right Radu. I have searched before, but just found querys to filter or not alpha-non. I have missed the other answer you told. I'll try your solution, thanks! – mEba Jul 03 '15 at 15:04
  • I didn´t test the solution yet, but the plan is to do this week. I´m a bit insecure because I never used funtions and variables in SQL. My questions is.. once I creat a function, MS SQL keeps it saved somewhere to use later or I should create it everytime I want to do the query ? THanks – mEba Jul 15 '15 at 16:18
  • Just updating. Works perfectly! Thanks! – mEba Jun 16 '16 at 04:16