August 22, 2018 at 7:35 pm
I have a Varchar field with units in inches 🙁 that i need to convert to an integer (Unfortunately the " and / characters are also within the field generated by a different application)
1"
3/4"
11/2"
13/4"
Any help appreciated
August 22, 2018 at 8:14 pm
Declare @Inches Table
(
TextVersion Varchar(10)
)
August 22, 2018 at 8:53 pm
thanks, i'm a novice, how do i use it
August 22, 2018 at 8:57 pm
what about string split? came across this here and working on test 2017 SQL Server
How do i select values from one column with string split ?
August 23, 2018 at 12:14 am
String_Split won't work in this case because it provides no guarantees about the order of rows, so it will happily split 3/4 into 3 and 4 but your can't tell that it wasn't 4/3 and in this case position is important (this is one of the most annoying limitations in SQL 2017 and one I really hope gets rectified in future).
As to how you use it, it really depends on exactly what you are trying to accomplish overall.
August 23, 2018 at 1:37 pm
bpmosullivan - Wednesday, August 22, 2018 7:35 PMI have a Varchar field with units in inches 🙁 that i need to convert to an integer (Unfortunately the " and / characters are also within the field generated by a different application)1"
3/4"
11/2"
13/4"Any help appreciated
The first question to ask is whether 11/2" is 1 and 1/2" or 5 and 1/2"? I suspect that it is in fact 1 and 1/2".
The next question, is whether there are any sizes Dividend is greater than 10, eg: 3/16".
From this we need to know whether 13/13" is 1 and 3/16" or 13/16"?
Finally, when working with 1 1/2" and 2 3/16", is there some form of separator between the whole number and the fraction?
August 23, 2018 at 3:35 pm
bpmosullivan - Wednesday, August 22, 2018 7:35 PMI have a Varchar field with units in inches 🙁 that i need to convert to an integer (Unfortunately the " and / characters are also within the field generated by a different application)1"
3/4"
11/2"
13/4"Any help appreciated
Fractions converted to integers will produce a value of zero. Are you simply saying that you want the numerator and denominator in separate columns? Please provide the desired output for the example you've given above.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2018 at 4:40 pm
bpmosullivan - Wednesday, August 22, 2018 7:35 PMI have a Varchar field with units in inches 🙁 that i need to convert to an integer (Unfortunately the " and / characters are also within the field generated by a different application)1"
3/4"
11/2"
13/4"Any help appreciated
I don't see how you can convert 3/4 to an integer?Declare @Inches Table
(
TextVersion Varchar(10)
)
Insert Into @Inches Values('1"'),('3/4"'),('11/2"'),('13/4"'),('2/3"')
Select TextVersion,
TextVersionNoQuote,
Numerator,
X.Denominator,
Y.Result
from @inches i
cross apply(values (replace(i.TextVersion,'"',''))) T(TextVersionNoQuote)
cross apply(values (charindex('/',T.TextVersionNoQuote))) U(SlashPos)
cross apply(values (IIF(U.SlashPos>0,U.SlashPos,10))) U1(D1)
cross apply(values (REPLACE(LEFT(T.TextVersionNoQuote,U1.D1-1),'/',''))) V(Numerator)
cross apply(values (SUBSTRING(T.TextVersionNoQuote,U1.D1+1,20))) W(Denominator)
cross apply(values (IIF(W.Denominator='','1',W.Denominator))) X(Denominator)
cross apply(values (CONVERT(money,V.Numerator)/CONVERT(money,X.Denominator))) Y(Result)
August 24, 2018 at 9:17 am
The biggest problem here is what value would one want when you have, say, 113/16" ??? Is that 1 and 13/16, or is it 11 and 3/16 ??? If you have no separation between the integer portion of the value and the fractional portion, you have absolutely NO WAY to code any amount of T-SQL or any other language that will solve that problem for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 24, 2018 at 10:47 am
bpmosullivan - Wednesday, August 22, 2018 7:35 PMI have a VARCHAR field [sic] with units in inches 🙁 that i need to convert to an integer (Unfortunately the " and / characters are also within the field [sic] generated by a different application)1"
3/4"
11/2"
13/4"Any help appreciated
Wouldn't it be a much better idea to get rid of these fractions, and replace them with decimals? That way you could do actual arithmetic with this column (it's a column not a field; get your copy of the ANSI/ISO SQL Standards and look up the definitions). This is as silly as using Roman Numerals, and it would be good if you can fix the source for this data as well.
In the US, the smallest fraction of an inch used in engineering and manufacturing is 1/64, which works out to 0.015625 as a decimal. This would be DECIMAL (n,6) . For whatever value of (n) make sense for your application. You can write a simple program to slice off the whole number and fraction, lookup the fractions value and put them together. The only problem is you also have to repeat this sort of lookup for 1/2, 1/4, 1/8, 1/16, and 1/32. This gives you a lookup table with less than 200 rows.
CREATE TABLE Proper_Fractions
(denominator INTEGER NOT NULL,
numerator INTEGER NOT NULL,
PRIMARY KEY (denominator, numerator),
CHECK (denominator > numerator),
decimal_value DECIMAL (12,6) NOT NULL);
If you have the time, get a good history of mathematics book and look up the stories of how decimal fractions came to be used (there were actually coins, currency, bonds, etc. denominated with proper fractions. Prior to decimalization in 1971 Britain used a system of pounds, shillings and pence. The smallest unit of currency was a penny, the plural of which was pence (or pennies). There were 12 pence in a shilling and 20 shillings in a pound. One of the classic programming problems back then was writing programs that could figure out, coin by coin, to put money in a pay envelope. The first version of PL/1 actually had built-in functions for this! Then they went decimal and didn't need this monster anymore.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 24, 2018 at 12:03 pm
jcelko212 32090 - Friday, August 24, 2018 10:47 AMWouldn't it be a much better idea to get rid of these fractions, and replace them with decimals? That way you could do actual arithmetic with this column (it's a column not a field; get your copy of the ANSI/ISO SQL Standards and look up the definitions). This is as silly as using Roman Numerals, and it would be good if you can fix the source for this data as well.In the US, the smallest fraction of an inch used in engineering and manufacturing is 1/64, which works out to 0.015625 as a decimal. This would be DECIMAL (n,6) . For whatever value of (n) make sense for your application. You can write a simple program to slice off the whole number and fraction, lookup the fractions value and put them together. The only problem is you also have to repeat this sort of lookup for 1/2, 1/4, 1/8, 1/16, and 1/32. This gives you a lookup table with less than 200 rows.
CREATE TABLE Proper_Fractions
(denominator INTEGER NOT NULL,
numerator INTEGER NOT NULL,
PRIMARY KEY (denominator, numerator),
CHECK (denominator > numerator),
decimal_value DECIMAL (12,6) NOT NULL);If you have the time, get a good history of mathematics book and look up the stories of how decimal fractions came to be used (there were actually coins, currency, bonds, etc. denominated with proper fractions. Prior to decimalization in 1971 Britain used a system of pounds, shillings and pence. The smallest unit of currency was a penny, the plural of which was pence (or pennies). There were 12 pence in a shilling and 20 shillings in a pound. One of the classic programming problems back then was writing programs that could figure out, coin by coin, to put money in a pay envelope. The first version of PL/1 actually had built-in functions for this! Then they went decimal and didn't need this monster anymore.
Man, I loved PL/1. It was way ahead of it's time. If you needed something "special", you could also make calls to Fortran and a couple of other languages kind of like what I use xpCmdShell for now.
And thanks for the history lesson... that's some interesting stuff. Heh... no wonder they went to the metric system. 😀 I don't know of anyone that has 6 digits on each hand. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2018 at 12:58 pm
Jeff Moden - Friday, August 24, 2018 12:03 PMMan, I loved PL/1. It was way ahead of it's time. If you needed something "special", you could also make calls to Fortran and a couple of other languages kind of like what I use xpCmdShell for now.
And thanks for the history lesson... that's some interesting stuff. Heh... no wonder they went to the metric system. 😀 I don't know of anyone that has 6 digits on each hand. 😀
Many decades ago, I was consulting at Coca-Cola in Atlanta. They were trying to convert COBOL over to PL/1 because it was the new cool super language. Since I'd taken computer classes at Georgia Tech, I knew Algol (the school was on a Burroughs 5500) , so my code was pretty good. Other programmers took advantage of this super language being able to be used COBOL or Fortran style. Their code frankly was pretty awful for a block structured language.
I really hated PL/1 because I thought it was overly complicated, and produced code that was about three times the size of the COBOL it was replacing. Apparently the IBM salesman for Coca-Cola the hell of a good job of selling extra hardware. Then after all that bitching I wound up working for DOD and learning ADA before there were even any compilers for it. I guess it could've been worse; somebody actually wrote in Algol 68 compiler over in the UK. I really hate super languages 🙁
Please post DDL and follow ANSI/ISO standards when asking for help.
August 26, 2018 at 4:37 pm
Man, I loved PL/1. It was way ahead of it's time. If you needed something "special", you could also make calls to Fortran and a couple of other languages kind of like what I use xpCmdShell for now.
It's very rarely that I disagree with you Jeff, but I think PL/1 was pure crap.
I came across it when it was still quite new, and asked IBM (back in 1968, when most of the various computer firms were still ready to help each other, and I worked for EE, a rival of IBM,but that didn't matter a damn when it came to helping each-other technically) for a specification or language defintion for PL1 to help me with my documentation of current computer languages. They sent me the documentation, no problem. I read the documentation, and concluded the language was crap - it was pure rubbish compared to CPL (and years later its successors BCPL, B, C and C++) which was itself rubbish anyway.
A couple of years later I defended PL1 (despite its awfulness) against some idiot who claimed that the guidance from IBM for assembly language components added to PL1 programs was wasteful. That resulted in recognition from my CS department head for my letter, and in thanks from a persom at IBM who remembered my contact with them a few years earlier (both my boss at the University and the guy I had had contact with at IBM agreed that PL1 was pure crap, but not as compeletely crap as the guy who attacked IBM's suggestions on assembly-level connections to PL1).
Tom
August 26, 2018 at 5:13 pm
PL/1 syntax was so irregular in it syntax they had to invent VDL (Viennese Definition Language) to replace the old BNF we’ve been using for languages. Parsing it required three passes over the source code, left to right, then right to left, then a final pass from left to right. One of the design principles of the language was that you didn’t need to have reserved words, so you could declare a variable named declare, etc
Decades ago, when I took my PL/1 class at Indiana University – Purdue University at Indianapolis we had a guy in the class we called the “mad bomber” who deliberately try to write the most awful programs he could to test out the features in the student version of the IBM PL/1 student compiler that we had on old IBM machine. We used punch cards and he simply typed his code in one continuous stream on them; no attempted indentation or pretty printing.
You probably guessed, from the nickname, how often his programs ran. But since this is the student compiler, it didn’t produce efficient code. It just gave a lot of detailed error messages for teaching purposes.
The operators learned to look for his deck of cards, put them aside, and run them last because they knew that job was going to crash. You don’t tie up the printer any longer than you have to.
The class assignment one night was to implement coroutines (writers and readers, producers and consumers, or whatever name you learned it under). The student compiler could have routine alpha calls routine beta and then beta calls alpha, etc. I am going to guess everybody remembers this exercise from their undergraduate days.
But the bomber wasn’t happy with this two-level solution and wanted to have a circle of three or four level coroutines. Being a student compiler, it allocated some kind of dope vector the invocation of each of the routines. The problem was PL/1 student version didn’t bother to check about cycles, so it just kept allocating some kind of link list structure until it filled up main storage. Then it went out and began filling up virtual storage. Then it hit the end of the physical memory of any kind. Finally, it hung in a loop trying to obtain enough storage to write out an error message.
The machine ran over the weekend because the operators put his deck in at the end of work on Friday. The machine burned itself out. The IBM repairman saved the core memory to take back to the home office because he had never seen anything like this before.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 26, 2018 at 6:09 pm
Tom
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply