Compare values to a mask on table
-
Hi everyone,I need to compare a value that I receive as a parameter to a mask in a table and get all records that match the value. To be more specific, I'm talking about account numbers.Let's say I have in my masks table where '#' means any number.
Creditor Mask MaskLength 123 54#### 6 124 455### 6 125 48#### 6 128 52###### 8 129 54####34 8 130 5423## 6 131 ###### 6 I have 34,000 different masks with lengths up to 22 characters long. If I receive '542322', I should get creditor 123, 130 and 131 but not 129 because is a different length. For '51234567' shouldn't find any and '52345678' should pull 128.Any ideas on how to do this?
-
Here's an example of something you can work with. It will return the correct results based off of your example, but you should pay close attention to the execution plan. I would imagine the you would want to create a covering index for this select.
declare @table table (Creditor int, Mask varchar(20), MaskLength int)
insert into @table
select 123,'54####',6 union all
select 124,'455###',6 union all
select 125,'48####', 6 union all
select 128,'52######', 8 union all
select 129,'54####34', 8 union all
select 130, '5423##', 6 union all
select 131, '######', 6
DECLARE @comparevalue varchar(20)
SET @comparevalue = '542322'
SELECT *
FROM @Table
WHERE MaskLength = LEN(@comparevalue) AND @comparevalue LIKE REPLACE(mask,'#','%')
-
John, thanks a lot. I didn't think it was going to work for creditor 129 because the mask has numbers after the '#' also, but it works fine. The Mask column has a Clustered index so i guess that would help a lot.
Doing some testing with different cases.
Thanks again
-
Happy for you, guys, but that solution is wrong.
Mask '1##5##' will match all these: '125235', '123451', '151111', '100005', etc.
Why not open BOL on topic "LIKE" and find this:
_ (underscore) Any single character. So, it nust be
WHERE @comparevalue LIKE REPLACE(mask,'#','_')
_____________
Code for TallyGenerator -
I did. Read about it and did use it on my testings. You are right. This is what must be used.
Thanks for posting it.
-
If the "#" is supposed to represent a digit, what happens if you introduce a letter or a space?
And, if you do it like below... you don't have to mess with length because the mask takes care of it auto-magically...SELECT *
FROM @Table
WHERE @comparevalue LIKE REPLACE(mask,'#','[0-9]')
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
"Happy for you, guys, but that solution is wrong."
Did you test it? While BOL suggests using the underscore for single character representation, the posted solution still works with % due to the length comparison. So to revise your statement "Happy for you, guys, but there are other solutions that will work too." As I stated in my post, my first look at this was a starting point and something to work with.
Raul, Jeff M.'s solution, to me, seems most optimal as it adds in a bit of validation as well as removing the need for the length check. Good Luck.
-
Did you test it?
DECLARE @Value nvarchar(50)
DECLARE @Mask nvarchar(50)
SET @Value = '150000'
SET @Mask = '1##5##'
SELECT 'OOOOOPS!', 'Value "' + @Value + '" is consistant with mask "' + @Mask + '"', 'Not right...'
WHERE @Value LIKE REPLACE(@Mask, '#', '%') AND LEN(@Value) = LEN(@Mask)
If I say you are wrong, that means you are wrong.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply