August 10, 2011 at 7:02 am
Hi, I'm working with the functional code below. I've seen similar code elsewhere on this forum with some useful answers so I thought I would post.
While this code produces the desired check digit for a given value of test data, I am unsure how to use this string to fill in a check digit column on another table. I can't usefully join the table dbo.tally to another table, so I don't know how to access both the table with the data I need to input/modify and the tally table at the same time.
My goal is to be able to use the code on a column of numbers in another table.
If someone could please explain to me how I could do this, or point me to a resource that would be helpful, I'd appreciate it.
declare @TestData varchar(12);
set @TestData = '0123456';
select
(10 - (sum(
(((N + 2) % 2) + 1) *
case
when substring(@TestData, N, 1) in ('0','A','K','U') then 0
when substring(@TestData, N, 1) in ('1','B','L','V') then 1
when substring(@TestData, N, 1) in ('2','C','M','W') then 2
when substring(@TestData, N, 1) in ('3','D','N','X') then 3
when substring(@TestData, N, 1) in ('4','E','O','Y') then 4
when substring(@TestData, N, 1) in ('5','F','B','Z') then 5
when substring(@TestData, N, 1) in ('6','G','Q') then 6
when substring(@TestData, N, 1) in ('7','H','R') then 7
when substring(@TestData, N, 1) in ('8','I','S') then 8
when substring(@TestData, N, 1) in ('9','J','T') then 9
else 0
end
) % 10)) % 10
from
dbo.Tally
where
N <= len(@TestData);
On a side note, am I correct that if my data is only number strings the inclusion of the letters in the substring checks in the case statement are uneccessary?
Thanks in advance.
August 10, 2011 at 8:29 am
Something like this get you pointed in the right direction?
create table #MyVals
(
val varchar(10)
)
insert #MyVals select '0123456'
insert #MyVals select '2342356'
insert #MyVals select '1234116'
insert #MyVals select '5017456'
select val, (10 - (sum((((N + 2) % 2) + 1) * cast(substring(val, N, 1) as int)) % 10)) % 10 as Calc
from #MyVals
cross join dbo.Tally
where N <= len(val)
group by val
drop table #MyVals
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2011 at 12:06 pm
Thank you for the response. I had not thought of just joining on no conditions. My worry is that, because I'm working with around 1 million rows of data, that temp table would be close to 10million rows, and I think that could cause problems.
Also, it ended up that the code I was using previously was not actualy returning the desired result. I ended up just breaking down the arithmatic functions of the check digit function that I need to apply, and, knowing that it only has to work for 7 digit number strings I wrote the code below. In this way I was able to avoid accessing a second table.
This code works and provides exactly the results I want, I've tested it on a sample table of strings and checked the results. It may, however, be the least elegant, most inefficent way of doing what I want to do imaginable. I worry that when I run it on the entire dataset things may explode.
Thanks in advance for any feedback
UPDATE testtable
SET checknumber =
right((10 -((convert (int, (substring(id, 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(id, 2, 1)))))), 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(id, 2, 1)))))), 2, 1)))+
convert (int, (substring(id, 3, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(id, 4, 1)))))), 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(id, 4, 1)))))), 2, 1))) +
convert (int, (substring(id, 5, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(id, 6, 1)))))), 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(id, 6, 1)))))), 2, 1))) +
convert (int, (substring(id, 7, 1)))) % 10)), 1)
FROM testtable
WHERE checknumber is null
*The issues with the first table were that, 1, it was doubling the wrong digits and 2, (more importantly) when adding the results together it treated 16 as 16 rather than as 1+6=7, which is how I need the function to work.
August 10, 2011 at 12:07 pm
the runnable version without that talbe is below.
declare @TestData varchar(12);
set @TestData = '2545996';
SELECT
right((10 -((convert (int, (substring(@TestData, 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(@TestData, 2, 1)))))), 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(@TestData, 2, 1)))))), 2, 1)))+
convert (int, (substring(@TestData, 3, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(@TestData, 4, 1)))))), 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(@TestData, 4, 1)))))), 2, 1))) +
convert (int, (substring(@TestData, 5, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(@TestData, 6, 1)))))), 1, 1))) +
convert (int, (substring ((convert(varchar(12), (2* convert (int, (substring(@TestData, 6, 1)))))), 2, 1))) +
convert (int, (substring(@TestData, 7, 1)))) % 10)), 1)
August 10, 2011 at 12:37 pm
I would be surprised if that would be faster than the tally table. That many converts has a lot of probability of being horrendously slow. Given a million+ records you might want to consider breaking up your job into chunks so you don't cripple your server for an hour or two while that thing runs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2011 at 12:47 pm
Ok thanks. I guess I can run both on some test batches and check the times. You're probably right.
Either way, this is going to be running at like 4 AM, but I'll look into batches.
Thanks for the input.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply