Search for account by account segment
-
Hello SQL Experts,
I have a table with account #'s ( Example : 20-200-300-4000)
I am using parameter in my query to search for the all account number or a single account, but they would also like to be able to search by segments ... If possible I would like for when the search text box appears on the screen it would look something like this
__-___-___-____ so the user can use mouse or cursor to move to a particular segment and type in, for instance 200 in the second segment as in the example above and all accounts with 200 in that segment is returned or 4000 in the last segment and all accounts with 4000 in the last segment is returned.similar search box example.
Any help on how to do the SQL search that will return all accounts with the entered segment will be appreciated.
Thanks
-
The best way to facilitate that would be to store the segments in separate columns.
-
I agree with Joe on the best approach, but if you have no control over the table you will need to use the substring, left and right functions of sql to process your searches.
if searching the 1st segment you can use the following
WHERE LEFT(ACCTNO, 2) = @searchValue
--or
WHERE SUBSTRING(ACCTNO,1,2) = @searchValue2nd Segment use SUBSTRING
WHERE SUBSTRING(ACCTNO,4,3) = @searchValue
etc
The last segment you can use substring or RIGHTWHERE SUBSTRING(ACCTNO,12,4) = @searchValue
--or
WHERE RIGHT(ACCTNO, 4) = @searchValue - Another option would be to create persisted indexed computed columns for each segment. However load test before deploying to production for crud operation performance degradation due to index overhead. Be certain to allow for addition storage overhead for the indexes and remember to alter any stored procedures accessing the table with the required set parameters, the same required for an indexed view.
-
Thank you all for your response... I really appreciate you expertise.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply