|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
wher statement to find a speciffic digit numberI wounder how i can find a speciffic digit numberin a nvarchar string in my table. Case: I have a costumer table. In there I have a column for IdentityNumber (nvarchar 255) - storing a 11 digit number. I want to make a select statement based on this column where the digit number 9 is an odd number. Select CostumerID from Costumer Where IdentityNumber .... ???? How do I proceed? Any help apreciates Best regards Hans SELECT customerid
FROM Customer WHERE identitynumber LIKE '________[13579]%' -- David Portas SQL Server MVP -- Assuming that IdentityNumber will always contain 11 digits, this should
work: select CostumerID from Costumer where substring(IdentityNumber, 9, 1) % 2 = 0 -- Show quote--Brian (Please reply to the newsgroups only.) "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... > Hi! > > I wounder how i can find a speciffic digit numberin a nvarchar string in > my > table. > > Case: > I have a costumer table. In there I have a column for > IdentityNumber (nvarchar 255) - storing a 11 digit number. > > I want to make a select statement based on this column where the digit > number 9 is an odd number. > > > Select CostumerID from Costumer Where IdentityNumber .... ???? > > How do I proceed? > > Any help apreciates > > > > Best regards > Hans CORRECTION:: It should be
select CostumerID from Costumer where substring(IdentityNumber,9,1) %2 = 1 That said, if you can't guarantee the string will always contain digits, I like David's solution using LIKE better. -- Show quote--Brian (Please reply to the newsgroups only.) "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message news:%23U9Z%23o7rFHA.2540@TK2MSFTNGP09.phx.gbl... > Assuming that IdentityNumber will always contain 11 digits, this should > work: > > select CostumerID from Costumer where substring(IdentityNumber, 9, 1) % 2 > = 0 > > -- > --Brian > (Please reply to the newsgroups only.) > > > "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message > news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... >> Hi! >> >> I wounder how i can find a speciffic digit numberin a nvarchar string in >> my >> table. >> >> Case: >> I have a costumer table. In there I have a column for >> IdentityNumber (nvarchar 255) - storing a 11 digit number. >> >> I want to make a select statement based on this column where the digit >> number 9 is an odd number. >> >> >> Select CostumerID from Costumer Where IdentityNumber .... ???? >> >> How do I proceed? >> >> Any help apreciates >> >> >> >> Best regards >> Hans > > Thank you for your help.
It worked excenelt :-D --------------- Best regards - Hans - Show quote "Brian Lawton" wrote: > CORRECTION:: It should be > > select CostumerID from Costumer where substring(IdentityNumber,9,1) %2 = 1 > > That said, if you can't guarantee the string will always contain digits, I > like David's solution using LIKE better. > > -- > --Brian > (Please reply to the newsgroups only.) > > > "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message > news:%23U9Z%23o7rFHA.2540@TK2MSFTNGP09.phx.gbl... > > Assuming that IdentityNumber will always contain 11 digits, this should > > work: > > > > select CostumerID from Costumer where substring(IdentityNumber, 9, 1) % 2 > > = 0 > > > > -- > > --Brian > > (Please reply to the newsgroups only.) > > > > > > "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message > > news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... > >> Hi! > >> > >> I wounder how i can find a speciffic digit numberin a nvarchar string in > >> my > >> table. > >> > >> Case: > >> I have a costumer table. In there I have a column for > >> IdentityNumber (nvarchar 255) - storing a 11 digit number. > >> > >> I want to make a select statement based on this column where the digit > >> number 9 is an odd number. > >> > >> > >> Select CostumerID from Costumer Where IdentityNumber .... ???? > >> > >> How do I proceed? > >> > >> Any help apreciates > >> > >> > >> > >> Best regards > >> Hans > > > > > > > Why do you have an nvarchar(255) column for storing an 11 digit number?
You could try using substring: WHERE SUBSTRING(IdentityNumber, 9, 1) IN ('1', '3', '5', '7', '9') Show quote "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... > Hi! > > I wounder how i can find a speciffic digit numberin a nvarchar string in my > table. > > Case: > I have a costumer table. In there I have a column for > IdentityNumber (nvarchar 255) - storing a 11 digit number. > > I want to make a select statement based on this column where the digit > number 9 is an odd number. > > > Select CostumerID from Costumer Where IdentityNumber .... ???? > > How do I proceed? > > Any help apreciates > > > > Best regards > Hans Thank you for your reply.
The reason is simple. In my system the identity is a digit number, but the database should bee generic, and then I dont know how the identity is in other systems --------------- Best regards - Hans - --------------- (Have fun programming with ... C#) Show quote "Brian Selzer" wrote: > Why do you have an nvarchar(255) column for storing an 11 digit number? > > You could try using substring: > WHERE SUBSTRING(IdentityNumber, 9, 1) IN ('1', '3', '5', '7', '9') > > "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message > news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... > > Hi! > > > > I wounder how i can find a speciffic digit numberin a nvarchar string in > my > > table. > > > > Case: > > I have a costumer table. In there I have a column for > > IdentityNumber (nvarchar 255) - storing a 11 digit number. > > > > I want to make a select statement based on this column where the digit > > number 9 is an odd number. > > > > > > Select CostumerID from Costumer Where IdentityNumber .... ???? > > > > How do I proceed? > > > > Any help apreciates > > > > > > > > Best regards > > Hans > > > Do you realize just how large a number with 255 digits is? You know that
walk from the car to your office on a rainy monday morning? That is just nuts to how big this number is. Think infinitely big, then just a bit smaller (well, maybe more than a bit, but it is pretty big.) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message news:1F57AB65-4024-46F3-8318-06434F5DCF4B@microsoft.com... > Thank you for your reply. > > The reason is simple. In my system the identity is a digit number, but > the > database should bee generic, and then I dont know how the identity is in > other systems > > > --------------- > Best regards > - Hans - > --------------- > (Have fun programming with ... C#) > > > "Brian Selzer" wrote: > >> Why do you have an nvarchar(255) column for storing an 11 digit number? >> >> You could try using substring: >> WHERE SUBSTRING(IdentityNumber, 9, 1) IN ('1', '3', '5', '7', '9') >> >> "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message >> news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... >> > Hi! >> > >> > I wounder how i can find a speciffic digit numberin a nvarchar string >> > in >> my >> > table. >> > >> > Case: >> > I have a costumer table. In there I have a column for >> > IdentityNumber (nvarchar 255) - storing a 11 digit number. >> > >> > I want to make a select statement based on this column where the digit >> > number 9 is an odd number. >> > >> > >> > Select CostumerID from Costumer Where IdentityNumber .... ???? >> > >> > How do I proceed? >> > >> > Any help apreciates >> > >> > >> > >> > Best regards >> > Hans >> >> >> An 11 digit number stored in a nvarchar column would be: 11 bytes for the
string x2 (becuase it's unicode) + a 2 byte header (becuase it's a varchar) for a total of 24 bytes! The same number as a BigInt would be 8 bytes. Show quote "Hans [DiaGraphIT]" <hans.ar***@sshf.no> wrote in message news:75688614-173E-491F-A013-35AD67FAE55D@microsoft.com... > Hi! > > I wounder how i can find a speciffic digit numberin a nvarchar string in > my > table. > > Case: > I have a costumer table. In there I have a column for > IdentityNumber (nvarchar 255) - storing a 11 digit number. > > I want to make a select statement based on this column where the digit > number 9 is an odd number. > > > Select CostumerID from Costumer Where IdentityNumber .... ???? > > How do I proceed? > > Any help apreciates > > > > Best regards > Hans  |
|||||||||||||||||||||||
Other interesting topics