Convert Varchar field

  • 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

  • Using the SQL 2012+ version of Jeff Moden's Delimited Split routine (which you can find on this site):

    Declare @Inches Table
    (
     TextVersion Varchar(10)
    )

    Insert Into @Inches Values('1"'),('3/4"'),('11/2"'),('13/4"')
    Select
     TextVersion,
     X.RealInches
    From @Inches
    Cross Apply
    (
     Select
      Max(Case When ItemNumber = 1 Then Cast(Item As Float) End)
      / IsNull(Max(Case When ItemNumber = 2 Then Cast(Item As Float) End), 1) As RealInches
     From dbo.DelimitedSplit8K_LEAD(Replace(TextVersion, '"',''),'/')
    ) X

  • thanks, i'm  a novice, how do i use it

  • what about string split? came across this here and working on test 2017 SQL Server

    https://blog.sqlauthority.com/2018/05/04/sql-server-split-comma-separated-value-string-in-a-column-using-string_split/

    How do i select values from one column with string split ?

  • 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.

  • bpmosullivan - Wednesday, August 22, 2018 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

    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?

  • bpmosullivan - Wednesday, August 22, 2018 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

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • bpmosullivan - Wednesday, August 22, 2018 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

    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)

  • 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)

  • bpmosullivan - Wednesday, August 22, 2018 7:35 PM

    I 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. 

  • jcelko212 32090 - Friday, August 24, 2018 10:47 AM

    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.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Friday, August 24, 2018 12:03 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.

    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. 

  • Jeff Moden - Friday, August 24, 2018 12:03 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

  • 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. 

  • jcelko212 32090 - Sunday, August 26, 2018 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

    jcelko212 32090 - Sunday, August 26, 2018 5:13 PM

    Many years ago, Cliff jones and I both applied for the same chair at Manchester.  After a very long delay, the University chose Cliff, not me (It took them a very long time to make up their mind who to take on - which seemed crazy to me once I knew Cliff was my competitor, because it seemed to me that someone with a leading role in the VDM project was streets ahead of me - I hadn't at that point realised how unreal VDM was, and nor did I until maybe a decade later).   But I never (and neither did Cliff) thought that VDM would replace BNF, VDM was nothing more than a fancy programming language with some ability to handle assertions and some important bits of semantic applications of syntax added to BNF, all attached to an Algol-like syntax. 
    Later on, Cliff and I both joined Harlequin at about the same time, Cliff as divsional director of the apps division and me as Cliff's divisional chief designer/architect/number 2 plus manager of one of his technology groups, and we had several discussions about VDL.  We had no disagreement about it - we both (the inventor and the critic)  thought it was in some ways misguided (actually I thought it was pure crap, as did most of OU's people, who preferred Z, as did IBM, but not most of ICL, for whom I'd worked for about 25 years).

    Tom

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply