April 9, 2020 at 9:23 pm
Hello,
I feel stupid asking this...I should be able to work itout, but here I am.
column value exsists in the table but when I selct value in a where , it retuns no records. the dattype is nvarchar and Ive tried to do a Cast and convert to no avail. am I missing somthing here ?
Any help apprciated - thanks.
Select *
FROM [EEST_App].[dbo].[artran]
where Cust_num = '5687'
--CAST( Cust_num AS nvarchar) = '5687'
-- CONVERT(nvarchar, Cust_num) = 5687
--'Z000135'
April 9, 2020 at 9:34 pm
Is it possible there's a leading space in the field including '5687' ?
Try using the following query:
SELECT *
FROM dbo.artran
WHERE Cust_num LIKE N'%5687';
April 9, 2020 at 9:41 pm
Oh my gosh..... that was it. Like worked
there are spaces. thank you !
April 9, 2020 at 9:44 pm
I'm glad I could help! I've been down that road before myself.
You're welcome!
April 9, 2020 at 9:48 pm
Is it a leading space or a leading TAB character?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2020 at 12:16 pm
Now add a CHECK(cust_num LIKE '[0-9][0-9][0-9][0-9]') to your DDL) so you do no have this problem again.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 10, 2020 at 4:26 pm
Will do... looks like space got in ther somehow -thank you !
April 10, 2020 at 6:45 pm
A better idea is to use the correct datatype. If the column is supposed to only handle digits, then changing the datatype of the column to be an INTEGER would eliminate the need for such checks and may save some disk and memory space in the process. If the column needs to contain something fixed width with leading zeros, do that at display time instead of storing it that way.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2020 at 10:28 pm
I think this is horrible advice. The customer number is an identifier for a customer (I assume), and not a magnitude or quantity of some attribute. Therefore, according to basic data modeling principles, it should be a character string. This string might have to be limited to digits, without special characters. Like Now like your Social Security number, credit card numbers and other things for which it is easy to turn them into numerics for computing check digits. The leading zeros can have great significance in such an encoding scheme. For example, in the ZIP Code system, leading zeros indicate a particular geographic quadrant of the United States. Taking the square root of the ZIP Code has no meaning, nor does adding constants to it. That's the nature of an identifier! Your desire to save small, cheap mounts of storage goes back to the 1950s and maybe early 1960s. Today in the 21st century, we like to follow Brett's rule store data the way it is used and use data the way it is stored. If you really think this is a problem, then look for compression algorithms or spend an extra few dollars to buy a slightly larger chunk of memory. Otherwise, you will waste a lot of time converting kludging then kludging and converting kludging and converting your data as you read it and when you write it.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 10, 2020 at 10:47 pm
>> A better idea is to use the correct datatype. ,<<
I agree with that statement. In fact read his posted this in a wonderful single principal, for which a given great credit. It is that store data the way it is used and use data the way it is stored. I wish I had said that
>> If the column is supposed to only handle digits, then changing the datatype of the column to be an INTEGER would eliminate the need for such checks and may save some disk and memory space in the process. <<
I'm sorry but did you mean "if the column is supposed to currently accept only digits", so we have no flexibility for alphanumeric's, punctuation or complete replacement some elaborate scheme of parsing the encoding?
I always keep a copy of an old book from 1836, "the North American arithmetic part the third for the advanced scholar," which you can download on project Gutenberg. It's just a fun read to see how things changed over the decades, but at the start of it. It defines numbers as representing quantities or magnitudes. We didn't have information theory or encoding scheme theory (actually started in 1947!), So what you're doing is fundamentally wrong
Back in the 1950s and maybe the early 1960s, storage was still insanely expensive. One of my favorite little memes is a picture of a forklift taking a disk drive of a cargo plane next to someone holding an older memory stick has more storage capacity than the huge physical IBM disk drive. Memory and storage are cheap. Access methods are insanely fast. It is very rare that you have to optimize the physical storage in the 21st century.
>> If the column needs to contain something fixed width with leading zeros, do that at display time instead of storing it that way. <<
What do those leading zeros have meaning? For example, leading zeros in the US postal systems ZIP Code indicate a particular geographic quadrant of the United States. Now I can try assure thar every program written from now until the end of the system, no to properly pad every ZIP Code every time. Over and over thousands, hundreds of thousands of times, millions of times whenever they see a three or four digit integer before they have converted it to a string.
And then when I pass my ZIP Code information to a second product or application, I'll have to guarantee that they also follow this conversion.
This is even weirder with remember that the ZIP Code is five characters, but an integer is usually eight characters (eight bytes) .
While I have not done the research, my observation has been that in ISO encoding schemes. The vast majority are significantly less than 16 characters long (look at your credit card numbers) and that most are five or fewer digits in length. In short, the use of integers in the real world for things that are by their very nature, character strings and identifiers actually cost storage, as well as making the program a hell of a lot harder.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 11, 2020 at 11:18 am
I think this is horrible advice. The customer number is an identifier for a customer (I assume), and not a magnitude or quantity of some attribute. Therefore, according to basic data modeling principles, it should be a character string. This string might have to be limited to digits, without special characters. Like Now like your Social Security number, credit card numbers and other things for which it is easy to turn them into numerics for computing check digits. The leading zeros can have great significance in such an encoding scheme. For example, in the ZIP Code system, leading zeros indicate a particular geographic quadrant of the United States. Taking the square root of the ZIP Code has no meaning, nor does adding constants to it. That's the nature of an identifier! Your desire to save small, cheap mounts of storage goes back to the 1950s and maybe early 1960s. Today in the 21st century, we like to follow Brett's rule store data the way it is used and use data the way it is stored. If you really think this is a problem, then look for compression algorithms or spend an extra few dollars to buy a slightly larger chunk of memory. Otherwise, you will waste a lot of time converting kludging then kludging and converting kludging and converting your data as you read it and when you write it.
All wrong. I hope nobody takes this flimsy nonsense seriously.
Go read this article (for the first time):
Perhaps you could point out the non-integer keys. There aren't any. Have a look at sections 2.2.1 Strong Redundancy and 2.2.2 Weak Redundancy. When used as foreign keys your natural keys violate one or both. Maybe that's what you mean by "basic data modeling." Basic to the point of unrealistic uselessness.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 11, 2020 at 4:52 pm
>> A better idea is to use the correct datatype. ,<<
I agree with that statement. In fact read his posted this in a wonderful single principal, for which a given great credit. It is that store data the way it is used and use data the way it is stored. I wish I had said that
Now it is you that is putting words in my mouth. 😉 That's not what I said. If people stored data as the way it would ultimately be used, then people would always store dates and times as strings, dollar amounts with "$" signs, and all identifiers that used dashes and leading zeros for display with those dashes and leading zeros being stored. I'm suggesting quite the opposite. That display junk should only appear in the presentation layer, not as a matter of rote in the database/storage area.
>> If the column is supposed to only handle digits, then changing the datatype of the column to be an INTEGER would eliminate the need for such checks and may save some disk and memory space in the process. <<
I'm sorry but did you mean "if the column is supposed to currently accept only digits", so we have no flexibility for alphanumeric's, punctuation or complete replacement some elaborate scheme of parsing the encoding?
When it comes to things like punctuation and leading zeros and dashes and embedded spaces, correct. That display junk should never be stored in a database especially since people don't always observe that punctuation when trying to do searches on the front end (which happens a whole lot). People frequently don't add the leading zeros or the punctuation and so Developers are relegated to doing leading wild-card searches on large amounts of data. Permanently storing things like leading zeros and punctuation is stupid. If a bank or some other organization suddenly decides that alphabetic characters must sudden be added to identifiers such as account numbers, it's normally because they want to include organizational information in the account number, which is a violation of even the first normal form. That junk should only be added at display time, just like leading zeros and punctuation.
I always keep a copy of an old book from 1836, "the North American arithmetic part the third for the advanced scholar," which you can download on project Gutenberg. It's just a fun read to see how things changed over the decades, but at the start of it. It defines numbers as representing quantities or magnitudes. We didn't have information theory or encoding scheme theory (actually started in 1947!), So what you're doing is fundamentally wrong
You should get up to speed, Joe. Numbers have been used very successfully and without the encoding that you speak of since before Bill Gates knew how to spell CPM. 😉
Back in the 1950s and maybe the early 1960s, storage was still insanely expensive. One of my favorite little memes is a picture of a forklift taking a disk drive of a cargo plane next to someone holding an older memory stick has more storage capacity than the huge physical IBM disk drive. Memory and storage are cheap. Access methods are insanely fast. It is very rare that you have to optimize the physical storage in the 21st century.
That's actually a huge misunderstanding on the part of today's programmers, analysts, and a whole raft of other job descriptions. For example, even the cloud is currently having difficulty with performance because they don't currently have enough memory, storage, or CPU to handle the extra workload caused by more people using the cloud because of the Covid-19 Pandemic. Memory isn't cheap if you need it right now and don't have any currently installed. The same goes for disk space, transmission rates, and CPU resources. Because of the incredible volumes of data that we deal with now, right-typing and right-sizing are more important than ever in just about every area you can think of. People that don't think or believe so are frequently the ones that show up on some forum bitching about how slow their code is and whining about how they can justify all new hardware only to find out that the new hardware (including huge numbers of CPUs and insane amounts of RAM and monster fast SSDs) didn't actually do a thing for performance because their data structure and the code that accesses it sucks.
>> If the column needs to contain something fixed width with leading zeros, do that at display time instead of storing it that way. <<
What do those leading zeros have meaning? For example, leading zeros in the US postal systems ZIP Code indicate a particular geographic quadrant of the United States. Now I can try assure thar every program written from now until the end of the system, no to properly pad every ZIP Code every time. Over and over thousands, hundreds of thousands of times, millions of times whenever they see a three or four digit integer before they have converted it to a string.
And then when I pass my ZIP Code information to a second product or application, I'll have to guarantee that they also follow this conversion.
This is even weirder with remember that the ZIP Code is five characters, but an integer is usually eight characters (eight bytes) .
While I have not done the research, my observation has been that in ISO encoding schemes. The vast majority are significantly less than 16 characters long (look at your credit card numbers) and that most are five or fewer digits in length. In short, the use of integers in the real world for things that are by their very nature, character strings and identifiers actually cost storage, as well as making the program a hell of a lot harder.
While it's true that roughly two thirds of the 5 digit integers represented by ZIP Codes won't fit in a 2 byte SMALL INT datatype (32767 is the limit) and would ordinarily require a common 4 byte integer (NOT 8 like you said), that hasn't been a problem since they came out with the VarDecimal storage format way back in SQL Server 2005. Of course, that has seamlessly been replace by Row and Page compression.
As for making the program a "hell of a lot harder", the only time that anything extra needs to be done to support leading zeros is during display time and that effort is trivial compared to the saving in memory, disk storage, I/O, transmission time, and computation time in SQL Server.
I really don't understand your objects to the things I've said about using integers and avoiding storing display junk such as leading zeros and punctuation. You're one of the people that pounds on others about proper normalization of data and not mixing the display and database layers.
You also really need to understand that there is absolutely no reason why numeric values cannot be used as identifiers. They even did that way back when you were a kid. 😀 It still works great today!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2020 at 7:22 pm
I completely agree with Celko.
At my place of work we had to import some employee data that came from 2 completely separate systems, one for temporary and one for permanent staff. Each had its own series of employee "numbers" - stored as a generic varchar, but using only digits and the occasional leading zero. Yes, far from perfect but good enough. This was a long time ago - anyone remember SQL 7?
Then the two employee systems were combined, and it was decided to keep the same numbering schemes, but now with a leading "T" or "P". I'm still grateful that all we had to do was adjust some validation logic on import..
April 24, 2020 at 7:46 pm
I completely agree with Celko.
At my place of work we had to import some employee data that came from 2 completely separate systems, one for temporary and one for permanent staff. Each had its own series of employee "numbers" - stored as a generic varchar, but using only digits and the occasional leading zero. Yes, far from perfect but good enough. This was a long time ago - anyone remember SQL 7?
Then the two employee systems were combined, and it was decided to keep the same numbering schemes, but now with a leading "T" or "P". I'm still grateful that all we had to do was adjust some validation logic on import..
You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just id#).
Didn't you have to change every table that had a linking key? How did you get around that when you've modified the identifier?
In a perfect world, you'd add a separate column. But that is often a lot of extra work. Instead you could have assigned new, and non-overlapping, numbers to whichever type of employees there were fewer of. Yes, you'd eventually have to update related (foreign) keys, if any, but you'd need to do that anyway, in some way or another, if you have overlapping numbers.
[When I was at International Paper, we bought (and sold) a lot of companies. Having to combine related tables from different approaches is another reason I don't automatically slap an identity column on every table. Not only does it cause fatal design issues and often cause serious performance issues, it also guarantees clashes when you try to combine two such tables.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2020 at 11:47 am
You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just id#).
Instead you could have assigned new, and non-overlapping, numbers to whichever type of employees there were fewer of.
Our database did not "own" the employee data. We merely received a revised daily extract from HR, and had no authority to redesign any of it.
In our database we used an identity column as the surrogate key for the table with the imported data, with a unique index on the imported employee ID. Not ideal but it worked well enough for over 10 years. I don't think that is too bad considering that it started out as a glorified spreadsheet to track a single project and eventually supported a couple of dozen automated in-house processes.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply