T-SQL Israeli CardID Checker / ת.ז. ישראלית

24 באוגוסט 2010

69 תגובות

T-SQL Israeli CardID Checker / ת.ז. ישראלית

Once in a while I come across code that I didn’t fins in the internet … mainly because it’s for local use 😉
This is one of these times 🙂

This code is the T-SQL version of the algorithm  that checks the given Israeli CardID , and adds a leading Zero , if need one ( like in my personal case ) , and calculates the “Check Number” ( SIFRAT BIKORET ) , if it’s missing.

I needed this for an SSIS Package that loaded monthly information needed for my SharePoint Content 😉

BTW , there might be a better way to code this , but i’m no T-SQL expert 🙂

/*
Algorithm From
http://halemo.net/info/idcard/index.html
*/

ALTER FUNCTION [dbo].[fn_FixUpCardID]
    (
    @CardID VARCHAR(50)
    )
RETURNS VARCHAR(50)
AS
    BEGIN
    DECLARE @CardID_1 INT
    DECLARE @CardID_2 INT   
    DECLARE @CardID_3 INT
    DECLARE @CardID_4 INT
    DECLARE @CardID_5 INT
    DECLARE @CardID_6 INT
    DECLARE @CardID_7 INT
    DECLARE @CardID_8 INT
    DECLARE @CardID_9 INT
    DECLARE @CardID_1_CalcVal INT
    DECLARE @CardID_2_CalcVal INT   
    DECLARE @CardID_3_CalcVal INT
    DECLARE @CardID_4_CalcVal INT
    DECLARE @CardID_5_CalcVal INT
    DECLARE @CardID_6_CalcVal INT
    DECLARE @CardID_7_CalcVal INT
    DECLARE @CardID_8_CalcVal INT
    DECLARE @CardID_9_CalcVal INT
    DECLARE @CardID_TotalValue INT
    DECLARE @CardID_Decimal INT
    IF (LEN(@CardID) = 7)
    BEGIN
       SET @CardID = '0' + @CardID;
    END
    SET @CardID_1 = CAST(SUBSTRING(@CardID,1,1) AS INT)
    SET @CardID_2 = CAST(SUBSTRING(@CardID,2,1) AS INT)
    SET @CardID_3 = CAST(SUBSTRING(@CardID,3,1) AS INT)
    SET @CardID_4 = CAST(SUBSTRING(@CardID,4,1) AS INT)
    SET @CardID_5 = CAST(SUBSTRING(@CardID,5,1) AS INT)
    SET @CardID_6 = CAST(SUBSTRING(@CardID,6,1) AS INT)
    SET @CardID_7 = CAST(SUBSTRING(@CardID,7,1) AS INT)
    SET @CardID_8 = CAST(SUBSTRING(@CardID,8,1) AS INT)
    if (LEN(@CardID) = 9)
    BEGIN
        SET @CardID_9 = CAST(SUBSTRING(@CardID,9,1) AS INT)
    END
    ELSE
    BEGIN
        SET @CardID_9 = -1
    END
    SET @CardID_1_CalcVal = (@CardID_1 * 1) / 10 + (@CardID_1 * 1) % 10
    SET @CardID_2_CalcVal =    (@CardID_2 * 2) / 10 + (@CardID_2 * 2) % 10
    SET @CardID_3_CalcVal = (@CardID_3 * 1) / 10 + (@CardID_3 * 1) % 10
    SET @CardID_4_CalcVal = (@CardID_4 * 2) / 10 + (@CardID_4 * 2) % 10
    SET @CardID_5_CalcVal = (@CardID_5 * 1) / 10 + (@CardID_5 * 1) % 10
    SET @CardID_6_CalcVal = (@CardID_6 * 2) / 10 + (@CardID_6 * 2) % 10
    SET @CardID_7_CalcVal = (@CardID_7 * 1) / 10 + (@CardID_7 * 1) % 10
    SET @CardID_8_CalcVal = (@CardID_8 * 2) / 10 + (@CardID_8 * 2) % 10
    SET @CardID_TotalValue = @CardID_1_CalcVal + @CardID_2_CalcVal +
                             @CardID_3_CalcVal + @CardID_4_CalcVal +
                             @CardID_5_CalcVal + @CardID_6_CalcVal +
                             @CardID_7_CalcVal + @CardID_8_CalcVal
    IF (@CardID_9 > -1)
    BEGIN
        SET @CardID_9_CalcVal = (@CardID_9 * 1) / 10 + (@CardID_9 * 1) % 10
         IF (((@CardID_TotalValue + @CardID_9_CalcVal) % 10) != 0)
                 SET @CardID = "
    END
    ELSE
    BEGIN
        if ((@CardID_TotalValue % 10) = 0)
        BEGIN
            SET @CardID = @CardID + '0'
        END
        ELSE
        BEGIN
            SET @CardID_9 = (((@CardID_TotalValue / 10) + 1) * 10) – @CardID_TotalValue
             SET @CardID = @CardID + CAST(@CardID_9 AS CHAR)
        END
       — SET @CardID = cast((((@CardID_TotalValue % 10) + 1) * 10) as varchar)
    END
    RETURN @CardID 
    END

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

69 תגובות

  1. Azoqk17 במאי 2012 ב 3:43

    So you have a class called Node with two inastnce variables called next and data. They are called inastnce variables because they belong to the inastnces of this class rather than to the class itself. That is, your class is basically a template (or blueprint) for objects that each willhave their own data value and next value.In order to create an inastnce of the Node class you need to call the constructor and pass the necessary parameters. In your case the constructor is; public Node(int d) { data = d; }To call this constructor you use the new keyword (in Java I assume) like this; Node x = new Node(10);And notice that you must supply an integer value to the constructor. In the body of the constructor (between the {}) you see that the variable data is assigned to the value in d which is the value that you pass to the constructor, in this example the value 10. You now have an object of type Node with the value 10 as it's data and a null next Node.On that object you can now call the method appendToTail(). Lets say we do this: x.appendToTail(20);Lets trace what happens. Node end = new Node(d);A new node named end is created and we set the value 20 to data (remember that d has the value 20 for now because that is the value we passed when we were calling the method). This is a completely independednt node from x with its own unique value for data. Node n = this;this is a self reference to the current object. Since we called this method on x then this is the same object as x. while (n.next != null) { n = n.next; }This while loop is going to start looking for the end of the list by going from the current node to the next node until the next node is null. Since the only node we created so far is x then n.next is actually null so the while loop does not execute this time. n.next = end;Now we are setting the next value of n (which is x) to the node end that was created. You now have a list like this: 10 -> 20 -> nullSuppose you were to make the following call: x.appendToTail(30);Then a similar thing happens except when you get to the while loop the value n.next is not null so you go into the body of the loop and assign n to point to n.next which in our example is the node with 20. The next iteration of the loop would yield the null so the loop will quit and the new node with data 30 will be set to the next value of the last node in the list. So you will have : 10 -> 20 -> 30 -> nullTo answer your questions:Q: since we set next' to be null when we first declare it, when does it get changed to not null? When you have only one item in the list, the next' value of that Node will be set to NULL.Q: and also what does Node n = this; mean? This statement means that the reference variable n' takes the reference of the current object, which is specified by this'.Q: Whenever we declare a new inastnce of the object Node, does it make a copy of its own separate fields from the class? Instance variables will be created for each individual class that you make an inastnce of. This means that every Node will have next' and data'.Thus, in your creation process, you will probably have something like this:Also, the while loop iterates to the end of the list and appends the item after the last node in the list.Hope it helps (: If you have any questions, do post back (:

    הגב
  2. Mccaskill15 בדצמבר 2012 ב 15:47

    I agree completely with what you said. Great Stuff. Keep it going.
    .

    הגב
  3. Bender9 בינואר 2013 ב 17:17

    Quality articles or reviews is the key to interest the visitors to pay a quick visit the site, that's what this website is providing.

    הגב
  4. Putnam24 בינואר 2013 ב 2:06

    It's very easy to find out any matter on web as compared to textbooks, as I found this post at this site.

    הגב
  5. Luttrell27 בינואר 2013 ב 2:29

    I don't know whether it's just me or if everybody else encountering issues with your blog.
    It appears like some of the text on your content are running off the screen.
    Can somebody else please provide feedback and let me know
    if this is happening to them too? This may be a
    issue with my browser because I've had this happen before. Cheers

    הגב
  6. Ault6 בפברואר 2013 ב 23:26

    Wow, this article is good, my sister is analyzing such things, thus I
    am going to tell her.

    הגב
  7. Walls10 בפברואר 2013 ב 19:52

    Very energetic blog, I enjoyed that bit. Will there be a part 2?

    הגב
  8. Charles11 בפברואר 2013 ב 4:29

    This paragraph will help the internet people for creating new website or even
    a blog from start to end.

    הגב
  9. Pyle12 בפברואר 2013 ב 12:43

    Excellent article. I am dealing with many of these issues as well.
    .

    הגב
  10. Sellers13 בפברואר 2013 ב 15:02

    Wow! At last I got a website from where I can in fact take valuable data concerning my study and knowledge.

    הגב
  11. Escobar16 בפברואר 2013 ב 10:34

    Very good article. I'm dealing with some of these issues as well..

    הגב
  12. Tackett17 בפברואר 2013 ב 19:28

    What's up, I would like to subscribe for this website to obtain latest updates, so where can i do it please assist.

    הגב
  13. Titus17 בפברואר 2013 ב 19:47

    This information is worth everyone's attention. Where can I find out more?

    הגב
  14. Odell18 בפברואר 2013 ב 6:43

    I visit daily a few blogs and websites to read articles or reviews, but this web site provides
    feature based posts.
    hardwood flooring

    הגב
  15. Fanning18 בפברואר 2013 ב 11:46

    always i used to read smaller content which also clear their motive, and
    that is also happening with this piece of writing which I am reading at this place.

    הגב
  16. Woodson18 בפברואר 2013 ב 21:57

    Hi, the whole thing is going fine here and ofcourse every one is sharing data, that's really good, keep up writing.

    הגב
  17. Phillips21 בפברואר 2013 ב 10:53

    It's truly very difficult in this active life to listen news on TV, so I simply use world wide web for that purpose, and obtain the most up-to-date information.

    הגב
  18. Smyth22 בפברואר 2013 ב 7:16

    Thanks for sharing your thoughts. I really appreciate your efforts and I am
    waiting for your further post thank you once again.

    הגב
  19. Phifer23 בפברואר 2013 ב 5:48

    What's up friends, how is everything, and what you want to say about this post, in my view its really awesome in favor of me.

    הגב
  20. Clarkson23 בפברואר 2013 ב 17:22

    I really like reading an article that will make people think.
    Also, thanks for allowing for me to comment!

    הגב
  21. Comstock1 במרץ 2013 ב 12:27

    Hi there everyone, it's my first pay a quick visit at this web site, and paragraph is truly fruitful in favor of me, keep up posting these types of articles.

    הגב
  22. Coats2 במרץ 2013 ב 8:22

    Superb, what a blog it is! This web site presents valuable facts to us,
    keep it up.

    הגב
  23. Rocha2 במרץ 2013 ב 19:18

    What's up colleagues, how is everything, and what you would like to say about this paragraph, in my view its in fact remarkable in favor of me.

    הגב
  24. Harlow6 במרץ 2013 ב 4:36

    I would like to thank you for the efforts you have put in writing this website.
    I am hoping to see the same high-grade blog posts from you in the
    future as well. In fact, your creative writing abilities has motivated me to
    get my very own blog now 😉

    הגב
  25. Soliz6 במרץ 2013 ב 21:09

    This website certainly has all of the information and facts I wanted concerning this subject and didn't know who to ask.

    הגב
  26. Clegg7 במרץ 2013 ב 3:07

    Do you have any video of that? I'd want to find out some additional information.

    הגב
  27. Hair7 במרץ 2013 ב 23:35

    Hello there! Would you mind if I share your blog with my twitter
    group? There's a lot of folks that I think would really appreciate your content. Please let me know. Many thanks

    הגב
  28. Jamieson12 במרץ 2013 ב 13:44

    This blog was… how do I say it? Relevant!

    ! Finally I have found something which helped me. Thanks!

    הגב
  29. Arrington16 במרץ 2013 ב 23:21

    Hey there! I've been following your website for a long time now and finally got the courage to go ahead and give you a shout out from Dallas Texas! Just wanted to say keep up the excellent job!

    הגב
  30. Michaels17 במרץ 2013 ב 14:41

    This is my first time visit at here and i am
    really impressed to read all at one place.

    הגב
  31. Stclair19 במרץ 2013 ב 22:31

    I'm not sure why but this site is loading extremely slow for me. Is anyone else having this problem or is it a problem on my end? I'll check back later and see if the problem still exists.

    הגב
  32. Darden20 במרץ 2013 ב 14:30

    So, start learning minibus-driving skills in a suitable place.
    This could save you a small fortune in minor damage
    claims. Many a times, what happens is that there are many people who keep visiting Essex more often.

    הגב
  33. Keys24 במרץ 2013 ב 2:27

    Over Coronado Bridge, you will be taken to see
    Mount Soledad and the Cabrillo Monument. Are HST and driver gratuity included.
    That is a very effective way to introduce the story and the character.

    הגב
  34. Pritchett25 במרץ 2013 ב 17:39

    I was able to find good information from your content.

    הגב
  35. Nickerson3 באפריל 2013 ב 14:39

    If you are on a business trip, it is of utmost importance that you reach your client.

    Cruises starting or ending in Honolulu offer passengers opportunities
    to visit Waikiki Beach, the North Shore, Pearl Harbor
    and much more. With many restaurants, cafes and bars
    this place has become a popular meeting joint.

    הגב
  36. Barrientos4 באפריל 2013 ב 3:33

    Would you describe for someone who isn't yet familiar with your site what Zen Habits is and why you decided to start it. Sadly for those adrenaline junkies that come here to take the challenge will soon be disappointed when the path is to be reconstructed to allow visitors to enter the gorge easily. Rarely need additional insurance: The rental car agencies often need to spend additional money per day on lifting and collision damage insurance coverage.

    הגב
  37. Klier13 באפריל 2013 ב 12:34

    My coder is trying to persuade me to move to .

    net from PHP. I have always disliked the idea because of the
    costs. But he's tryiong none the less. I've been using Movable-type on several websites for about a year
    and am worried about switching to another platform. I have heard excellent things about blogengine.
    net. Is there a way I can import all my wordpress content into it?
    Any help would be greatly appreciated!

    הגב
  38. Andre20 באפריל 2013 ב 15:29

    I think the admin of this site is really working hard in support of his web
    page, for the reason that here every data is quality based stuff.

    הגב
  39. Perrin20 באפריל 2013 ב 15:30

    Hi there to every single one, it's really a good for me to go to see this web page, it contains valuable Information.

    הגב
  40. Hair20 באפריל 2013 ב 21:13

    Heya just wanted to give you a quick heads up and let you know a few
    of the images aren't loading correctly. I'm
    not sure why but I think its a linking issue. I've tried it in two different browsers and both show the same results.

    הגב
  41. Royer21 באפריל 2013 ב 0:20

    Howdy are using WordPress for your blog platform? I'm new to the blog world but I'm
    trying to get started and set up my own. Do you require any html coding knowledge to make your own blog?
    Any help would be really appreciated!

    הגב
  42. Low21 באפריל 2013 ב 4:38

    Keep on working, great job!

    הגב
  43. Puckett21 באפריל 2013 ב 8:22

    I'm gone to convey my little brother, that he should also go to see this website on regular basis to obtain updated from latest news update.

    הגב
  44. Buckner21 באפריל 2013 ב 15:24

    Does your site have a contact page? I'm having problems locating it but, I'd like to shoot you an email.
    I've got some suggestions for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it improve over time.

    הגב
  45. Deboer21 באפריל 2013 ב 16:57

    I hardly write responses, but i did a few searching and wound up here T-SQL Israeli CardID Checker
    / ת.ז. ישראלית – Adlai Maschiach.

    And I do have some questions for you if you do not mind.
    Could it be only me or does it give the impression like some of these comments look like they
    are coming from brain dead folks? 😛 And, if you
    are posting on other online sites, I would like to keep
    up with anything fresh you have to post. Could you make a list of all of your shared sites like your twitter feed, Facebook page or linkedin profile?

    הגב
  46. Major21 באפריל 2013 ב 18:16

    What's up, of course this post is in fact fastidious and I have learned lot of things from it regarding blogging. thanks.

    הגב
  47. Vitale21 באפריל 2013 ב 18:22

    I don't know whether it's just me or if perhaps everyone else encountering issues with your blog.
    It appears as if some of the written text within your posts
    are running off the screen. Can somebody else please comment and let me know if
    this is happening to them too? This could be a issue with my web browser because I've had this happen before. Many thanks

    הגב
  48. Rowan21 באפריל 2013 ב 18:22

    I don't even know the way I ended up here, but I thought this submit used to be great. I don't recognise who you might be but certainly you
    are going to a well-known blogger if you happen to are not already.
    Cheers!

    הגב
  49. Bone21 באפריל 2013 ב 22:56

    When I initially commented I clicked the "Notify me when new comments are added" checkbox and now each time a comment is added I get
    several e-mails with the same comment. Is there any way you can remove me from that
    service? Thank you!

    הגב
  50. Branch21 באפריל 2013 ב 23:34

    Greetings from Colorado! I'm bored at work so I decided to check out your blog on my iphone during lunch break. I really like the information you present here and can't
    wait to take a look when I get home. I'm amazed at how fast your blog loaded on my mobile .. I'm not
    even using WIFI, just 3G .. Anyhow, amazing site!

    הגב
  51. Mccauley22 באפריל 2013 ב 10:46

    Greetings! This is my first comment here so I just wanted to give a quick shout out and tell you I
    genuinely enjoy reading through your blog posts. Can you recommend any other
    blogs/websites/forums that cover the same subjects? Thanks!

    הגב
  52. Mcgregor2 במאי 2013 ב 12:27

    Wonderful goods from you, man. I've understand your stuff previous to and you are just extremely wonderful. I actually like what you have acquired here, certainly like what you're saying and the way
    in which you say it. You make it entertaining and you still
    care for to keep it wise. I can not wait to read
    far more from you. This is really a tremendous site.

    הגב
  53. Fiore2 במאי 2013 ב 12:28

    Good day I am so happy I found your site, I really found you by accident,
    while I was looking on Google for something else, Regardless I am here
    now and would just like to say thanks for a remarkable post and a all round enjoyable blog
    (I also love the theme/design), I don't have time to browse it all at the moment but I have bookmarked it and also included your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the excellent work.

    הגב
  54. Coyne2 במאי 2013 ב 16:08

    With havin so much content do you ever run into any
    problems of plagorism or copyright infringement?

    My site has a lot of unique content I've either authored myself or outsourced but it looks like a lot of it is popping it up all over the web without my authorization. Do you know any techniques to help stop content from being stolen? I'd really appreciate
    it.

    הגב
  55. Mueller2 במאי 2013 ב 23:33

    You can gain a lot of information regarding the best company that can
    provide a rent a car service for you. This will give an amazing view of the beautiful
    Mar Menor lagoon and the Mediterranean coast. Furthermore rental car services
    focus on travelers; they also cater to those people whose cars
    are temporarily out of service, damaged or destroyed, and also to those
    who might be waiting for an insurance renewal.

    הגב
  56. Mccormick3 במאי 2013 ב 2:04

    If you desire to increase your know-how only keep visiting this site and
    be updated with the newest news update posted here.

    הגב
  57. Victor4 במאי 2013 ב 18:33

    WOW just what I was looking for. Came here by searching for
    security alarm

    הגב
  58. Nielsen5 במאי 2013 ב 7:18

    Saved as a favorite, I love your site!

    הגב
  59. Dorris5 במאי 2013 ב 17:16

    What a data of un-ambiguity and preserveness of valuable familiarity
    about unpredicted emotions.

    הגב
  60. Ma7 במאי 2013 ב 2:20

    You can definitely see your expertise in the article you write.
    The arena hopes for more passionate writers such as you who are not afraid to
    say how they believe. At all times follow your
    heart.

    הגב
  61. Tompkins8 במאי 2013 ב 20:04

    Hmm is anyone else experiencing problems
    with the pictures on this blog loading? I'm trying to find out if its a problem on my end or if it's the blog.

    Any feedback would be greatly appreciated.

    הגב
  62. Stanton8 במאי 2013 ב 21:39

    This post is truly a fastidious one it assists new internet people, who are wishing in favor of
    blogging. Drive more moreattract Targeted visitors by means of twitter
    followers buy on your Report successful way.
    If you wish to buy followers on twitter make sure
    you maintain the twitter accounts updated.

    If you need to buy twitter followers to your twitter consideration, there are numerous quite effective ways.

    הגב
  63. Kirkwood9 במאי 2013 ב 4:03

    My family members every time say that I am wasting my time here at net, except I know I am getting knowledge all the time by reading thes
    pleasant content. buy followers on twitter
    twitter for business can appreciably improve ad�quation as well as believability.

    more followers on twitter Inexpensive along with Protected for twitter
    account.

    הגב
  64. Reeder15 במאי 2013 ב 18:40

    Hi mates, its wonderful article regarding educationand entirely defined,
    keep it up all the time.

    הגב
  65. Minnick15 במאי 2013 ב 20:49

    It's nearly impossible to find educated people in this particular subject, however, you sound like you know what you're
    talking about! Thanks

    הגב
  66. Wentworth18 ביוני 2013 ב 3:10

    What's up, after reading this remarkable post i am too glad to share my know-how here with friends.

    הגב
  67. Baumgartner12 באוגוסט 2013 ב 15:48

    You have made some decent points there. I looked on the net for more info about the
    issue and found most individuals will go along with your views on this web site.

    הגב
  68. Gordon14 באוגוסט 2013 ב 23:32

    Amazing issues here. I am very glad to peer your article. Thank you so much and I
    am having a look forward to touch you. Will you kindly drop me a e-mail?

    הגב
  69. Briley15 באוגוסט 2013 ב 14:31

    When some one searches for his necessary thing, so he/she wants to
    be available that in detail, so that thing is maintained over here.

    הגב