July 7, 2009 at 3:30 pm
Hello,
I need to compare two strings one which has a place holder of 'XXXXXXXXXX' and one which has a number where the place holder would be in the string eg;
Incoming String:
An error occurred on acct no: 1234567890. For further information refer to http://something.com/answers.aspx?id=234
Place holder string:
An error occurred on acct no: XXXXXXXXXX. For further information refer to http://something.com/answers?id=234
The 'account no:' in the 'Incoming String' will vary and the place holder string may have more information based on the error encountered eg;
An error occurred on acct no: XXXXXXXXXX. For further information refer to http://something.com/answers.aspx?id=234|5434 Alert incoming.
How can I compare the two strings and get an exact match?
Any help will be greatly appreciated.
Brian.
July 8, 2009 at 4:12 pm
You'll have to decide EXACTLY what you mean by "exact match", and you need to be as anal about that as is possible. You either need to know every possible alternative set of text, or, you'll need to be able to classify a specfic string as matching a pattern. For example, if the account number is always preceded by a single quote as well as followed by one, and is always preceded by "acct. no:", then you could write code that finds the location of that first single quote, then the location of the second one, and then replace all text between those two positions. It will take a number of steps, but it should be doable. You're going to have to acknowledge that it may not be possible to absolutely guarantee that you might not, at some time in the future, come across a string that isn't supposed to be a match, but still passes your test, so you need to be as specific as you can. If you can specify exactly what rules your pattern needs to encompass, we can help you get the code needed to implement those rules, but until you decide what the EXACT rules are going to be, there's no magic statement that can decide that two long strings are necessarily sufficiently similar to qualify as "exact match" for your purposes.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2009 at 5:39 pm
Thanks Steve,
I've been messing about with PATINDEX and LEFT etc. and because I'm fairly new to SQL Server, getting my head around it is proving problematic, to say the least.
I think what I am trying to achieve is the incoming account number is to be replaced by an X for each digit in it then compared to a 'lookup' (oops, my excel training is showing) table to find it's match.
The biggest problem I've got is that the text that is both before and after the account number may vary, but the account number itself will always be a ten digit number.
So, would the rule be that if there is a 10 digit number in the string, replace it with ten X's and then compare that result with what's in the lookup table?
Apologies for my blatant newbee-isms.
Brian.
July 8, 2009 at 5:39 pm
Thanks Steve,
I've been messing about with PATINDEX and LEFT etc. and because I'm fairly new to SQL Server, getting my head around it is proving problematic, to say the least.
I think what I am trying to achieve is the incoming account number is to be replaced by an X for each digit in it then compared to a 'lookup' (oops, my excel training is showing) table to find it's match.
The biggest problem I've got is that the text that is both before and after the account number may vary, but the account number itself will always be a ten digit number.
So, would the rule be that if there is a 10 digit number in the string, replace it with ten X's and then compare that result with what's in the lookup table?
Apologies for my blatant newbee-isms.
Brian.
July 8, 2009 at 5:40 pm
Thanks Steve,
I've been messing about with PATINDEX and LEFT etc. and because I'm fairly new to SQL Server getting my head around it is proving problematic, to say the least.
I think what I am trying to achieve is the incoming account number is to be replaced by an X for each digit in it then compared to a 'lookup' (oops my excel training is showing) table to find it's match.
The biggest problem I've got is that the text that is both before and after the account number may vary, but the account number itself will always be a ten digit number.
So, would the rule be that if there is a 10 digit number in the string, replace it with ten X's and then compare that result with what's in the lookup table?
Apologies for my blatant newbee-isms.
July 10, 2009 at 6:15 am
I hear you on the newbee-isms. I was at that point a lot of years ago. First point is that you can't wait until AFTER you've replaced that account number to do the lookup. Also, you need to start thinking in sets instead of the row-by-row concept that the word lookup implies. When you have data like that, your objective is to have a formula that gets you that account number in a datatype of the same type as the field in the table where you'd be looking it up, and then your lookup becomes a JOIN to that table on that field. You'll need to post some code and the table definitions you already have for us to be able to help much further. Show us how far you've gotten, and we'll help you get the rest of the way there.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply