Remove Zero(s) from Search

[danieljoo]

How can I remove zeros in my search? ie search for "000103" returns "103". Here is my search code:


<%
' Get search criteria for basic search
pSearch = trim(Request.QueryString(&quot;psearch&quot)
If pSearch <> &quot;&quot; Then
pSearch = replace(pSearch,&quot;'&quot;,&quot;''&quot
pSearch = replace(pSearch,&quot;[&quot;,&quot;[[]&quot
b_search = b_search & &quot;[CustomerNumber] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerName] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerAddress] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerCity] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerState] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerZip] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerGrpCode] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerOwnersCode] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
b_search = b_search & &quot;[CustomerSalesRep] LIKE '%&quot; & pSearch & &quot;%' OR &quot;
End If
If len(b_search) > 4 Then
b_search = mid(b_search,1,len(b_search)-4)
b_search = &quot;(&quot; & b_search & &quot&quot;
End If
%>

<%
'Build search criteria
If a_search <> &quot;&quot; Then
dbwhere = dbwhere & a_search 'advance search
ElseIf b_search <> &quot;&quot; Then
dbwhere = dbwhere & b_search 'basic search
End If

'Save search criteria
If dbwhere <> &quot;&quot; Then
Session(&quot;tablename&quot = tablename
Session(&quot;dbwhere&quot = dbwhere
'reset start record counter
startRec = 1
Session(&quot;Customer_REC&quot = startRec
Else
If tablename = Session(&quot;tablename&quot Then
dbwhere = Session(&quot;dbwhere&quot
Else
'reset search criteria
dbwhere = &quot;&quot;
Session(&quot;dbwhere&quot = dbwhere
End If
End If

'Get clear search cmd
If Request.QueryString(&quot;cmd&quot.Count > 0 then
cmd=Request.QueryString(&quot;cmd&quot
If ucase(cmd) = &quot;RESET&quot; Then
'reset search criteria
dbwhere = &quot;&quot;
Session(&quot;dbwhere&quot = dbwhere
End If
End If

%>

<%
strsql = &quot;select * from [Customer]&quot;
If dbwhere <> &quot;&quot; Then
strsql = strsql & &quot; WHERE &quot; & trim(dbwhere)
End If
if OrderBy <> &quot;&quot; then
strsql = strsql & &quot; ORDER BY [&quot; & OrderBy & &quot;] &quot; & Session(&quot;Customer_OT&quot
end if
%>

<input type=&quot;Text&quot; name=&quot;psearch&quot; size=10>
<input type=&quot;Submit&quot; name=&quot;Submit&quot; value=&quot;GO!&quot;>

 

[Tarwn]

If I am understanding correctly , then your example was what you are trying to achieve, rather then what you are trying to prevent, correct?

In this case what we would want to do is create a custom trim function that would allow us to find the position of the first number in the string that is greater than 0. Once we have that then we can simply take the substring of the string from that location on and use that in our search.

The easiest way I can think of to find that location is to use a Regular Expression. The bonus of using a regular expression is that we can actually craft it to do the 'trimming' for us.

1. We need to set up a pattern that will allow any number of 0's followed by any non-zero, followed by any number of digits from 0 to 9.
Dim pattern
pattern = "0*[1-9][0-9]*"

2. Next we will want to group the digits in such a way that we will be able to get back everything but the first set of 0's (if there are any).
pattern = "(0*)([1-9][0-9]*)"

3. The next part will be to actually create a RegExp object and assign our pattern string to the objects pattern attribute. If you need more information on using the RegExp object, I have found this article and it's related articles to be of much use in the past: [URL unfurl="true"]http://www.4guysfromrolla.com/webtech/090199-1.shtml[/URL]

4. Now what we need to do is use a built in method of the RegExp object to produce a string that contains only the second part of our search word, the part without the preceding 0's. The easiest to do this is to simply get the matches (there should only be one), you can find examples of this in the second part of the above linked article.

I hope I understood what you were looking for correctly, if you have any more questions, feel free to ask,
-Tarwn ________________________________________________
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048