String Matching by Position and Valid Values

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

SQL Server 2000 Forums

SQL Server Development (2000)

String Matching by Position and Valid Values

Author

Topic

dmcknight5
Starting Member

8 Posts

Posted - 2009-05-01 : 12:14:49

I have a process that I'm working on, and I need to following assistance.

I have a column that stores account codes and the user wants to be able to enter in any part of the account code and only return the account matches that match the values
at the positions the user entered valid values. The account code's full length is 5 (really more, but for this example 5) and for the values that are unknown the user is entering a "?" to
pad the account code to its full length.

Example

Acct #
12345 --Opt1
22345 --Opt2
56346 --Opt3

User enters in parameter report "1?3??" (the question marks are simply place holders when the user doesn't know the entire account number).
From the list above the user should receive only Opt1, but if the user enters ??3?? Opt1-3 should be returned. Nevertheless, if the user enters all valid values then I match on the complete string not partially.

I'm thinking that I need to match the valid values from the parameter and somehow determine how to match the positions from the parameter that are valid to the positions from column....however, I'm lost on how to set this up correctly.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-01 : 12:53:26

[code]DECLARE @match varchar(10)
SET @match='??3??'
SELECT * From myTable WHERE Acct LIKE REPLACE(@match, '?', '_')[/code]The _ character is a single character wildcard match.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-01 : 12:57:01

I would assume that account number is an INT, but here is a sample that might help you solve your issue using the LIKE clause:

DECLARE @Acct TABLE (AccountNum VARCHAR(20), Name VARCHAR(20))
INSERT @Acct
SELECT '12345', 'Opt1'
UNION ALL SELECT '22345', 'Opt2'
UNION ALL SELECT '56346', 'Opt3'


DECLARE @Pattern VARCHAR(20)

SET @Pattern = '1?3??'
-- SET @Pattern = '??3??' 
SET @Pattern = REPLACE(@Pattern, '?', '_')

SELECT *
FROM @Acct
WHERE AccountNum LIKE @Pattern

EDIT: damn too slow again. :)

dmcknight5
Starting Member

8 Posts

Posted - 2009-05-01 : 13:01:34

Thank You!

I was making it too complicated. However, are there any drawbacks to this approach? I know that using the "Like" functionality doesn't always return what's expected.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-01 : 15:01:42

There can be severla drawbakcs: if your data is stored as INTs then there is conversion overhead and lack af sargability (cannot use indexes).

As for what is expected.. It alwasy returns what you tell it to. If you expect something differrent then you told it to get the wrong things.. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources