Select statement by ignore dash ('-')

Question

Select statement by ignore dash ('-')

molecule_kaab

SSC Eights!

Points: 826
More actions
December 1, 2008 at 4:33 am

#203637

Hi All,
I problem of creating sql statement to query the data about product code containning dash but when the client want to do search, he may ignore dash in his keyword.
For example
id Code name
-----------------------------
1 101-204 Black T-shirt
2 102-204 White T-shirt
if the client enter 101204 it should show the Black T-shirt as the result.
Anyone can suggest me how to do this
Thank in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ronald H SSCarpal Tunnel Points: 4023 More actions · Answer 1

I took from what you wrote that if the user either have the input with or without the dash, it should give the desired result. If you want something else, please follow the link in my signature to post the table structure.SELECT ID, Code, Name

FROM Table1

WHERE '101204' IN (REPLACE(Code,'-',''),Code)

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

molecule_kaab SSC Eights! Points: 826 More actions · Answer 2

This is what I'm looking for 🙂

Thank you for your quick reply.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

The only issue with Ronald's post is that using a function (Replace) in the where clause negates any indexes you may have on the code column.

If the code is always 3 characters + "-" + 3 characters then I would insert the dash in the 4th position id it not already there, like this:

If CharIndex('-', @code) = 0 -- not found

Begin

Set @code = Left(@code, 3) + '-' + Right(@code, 3)

End

Then use the @code parameter in the where clause which will allow the optimizer to choose to use the index as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

molecule_kaab SSC Eights! Points: 826 More actions · Answer 4

Thank you for your solution.

But the code is not in the same pattern, They may be 11-220, 201-223, 11-23-2.

However, this is another good solution, thank you again 🙂

Jack Corbett SSC Guru Points: 184388 More actions · Answer 5

molecule_kaab (12/1/2008)
Thank you for your solution.
But the code is not in the same pattern, They may be 11-220, 201-223, 11-23-2.
However, this is another good solution, thank you again 🙂

That's why I threw in the "IF".

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sgmunson SSC Guru Points: 110588 More actions · Answer 6

I'm thinking that there could be a potential problem with this scenario, because there may be more than one record that satisfies a search for 123456, as one could have 12-345-6 or 123-45-6, or any of a number of other patterns. It may or may not be a problem to return multiple results, but how about adding a computed field to the table, that duplicates the original field but strips out all the dashes. Then you place an index on it, but not a unique one. This new field can then be used for searches without having to worry about the position of the dashes.

Steve

(aka smunson)

:):):)

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)