MS SQL Query? How to Ignore (-) Hyphen in String

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

Development Tools

ASP.NET

MS SQL Query? How to Ignore (-) Hyphen in String

Author

Topic

Nebuli
Starting Member

2 Posts

Posted - 2006-04-04 : 15:05:46

Hello,

I am using an Access database that has various model#'s that contain hyphens in various locations. e.g. model TC-5998-ATU or TC5998-DV. I want users to be able to type in TC5998ATU and not come up with an invalid search. Can anyone please help me with this problem?This form was programmed using ASP <--not sure if this makes a difference in my sql query. I really appreciate any help that can be offered.

Jason

My current search string is as follow:
(the problem with the code below is that when the user types in a search string, the hyphens need to be placed in the exact location or the query will be invalid)

strKeyword=Request.Form("keyword")
strNo=Request.Form("No")

if strKeyword<>"" then
if strNo="" then strNo="0"

strSQL1 = " select showno from prodlist where "
strSQL1 = strSQL1 & " Category like '%" & strKeyword &"%'"
strSQL1 = strSQL1 & " or SubCategory like '%" & strKeyword &"%'"
strSQL1 = strSQL1 & " or Type like '%" & strKeyword &"%'"
strSQL1 = strSQL1 & " or ModelNo like '%" & strKeyword &"%'"
strSQL1 = strSQL1 & " or ModelName like '%" & strKeyword &"%'"
strSQL1 = strSQL1 & " or Features like '%" & strKeyword &"%'"
strSQL2 = " select showno from Specifications where Description like '%" & strKeyword &"%'"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 15:17:40

Check whether the Replace do it for u. eg. Select replace('TC-5998-ATU','-','')

strSQL2 = " select showno from Specifications where replace(Description,'-','') like '%" & strKeyword &"%'"

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 15:22:10

I wouldn't remove the hypens in the query. I'd make an adjustment to your strKeyword variable instead. It would make your code easier to read. Keep the query as simple as possible.

Tara Kizer
aka tduggan

Nebuli
Starting Member

2 Posts

Posted - 2006-04-04 : 19:13:40

I will have to give that replace a shot to see the outcome. Thank you for responding so quickly

In regards to the previous response, it is not that I really want the hyphens removed, but I want the search string to pull the information where ever the hyphens may be placed in the database. In other words, over look the hyphens in the database and pretend that they are not there. What would you suggest the strKeyword variable should look like?

Thanks All!

Jason

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-05 : 05:22:47

how about
"select showno from prodlist where replace(Category,'-','') like '%" & strKeyword &"%'"...etc"

you will have poor performance because the function will negate the use of any indices....but the original wildcard searching seems to point to that result anyway. Alternatively have 2 columns, 1 with formtting and 1 without...(does access support derived columns?) searching on a column which is the result of the "replace" function may be the way to go...IF supported.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources