locked
Error Converting Varchar (Curly Bracket) To Numeric RRS feed

  • Question

  • I'm having trouble parsing a file for import. I get "Error converting varchar to float" and it looks like it's because the incoming file has a curly bracket { at the end of each numeric field. Presumably a sign indicator (positive = {/negative = }) although I haven't encountered and right curly brackets }?.

    I feel like I can't be the first person to have this problem but I can't find an answer searching the forum. Any help is very much appreciated. Is this a common issue importing to SQL? What's the best practice?

    The file spec shows a data picture of "s9(04)v9(03)" but all I understand from that is that it's a 7 character numeric field with 4 digits left of the decimal and 3 to the right.

    The incoming text is 132 character fixed width, it looks like this:

    Edit: the first numeric field in each row is in positions 21 to 27, it reads 000200{ in the incoming file in the first row, so 0002.000 after dividing by 1000 to convert for the decimal in the spec. correct, verifiable values in the first numeric field for these records are: 2.000, 1.100, 1.120, 2.880, and 1.000.

    A000001B639CJ10810 4000200{0000000         00000000{00000000{BUDFF            00000000000000000000000000000000000          11      X
    A000007G0084W10110 1000110{0000000         00000000{00000000{ADNT             00000000000000000000020181072018135        1111  3   X
    A000008G0129K10410 1000112{0000000         00000000{00000000{AYR              00000000000000000000020180582018074        1111  3   X
    A000009G0177J10810 1000288{0000000         00000000{00000000{AGN              00000000000000000000020180582018087        1111  3   X
    A000010G0260210310 8000100{0000000         00000000{00000000{DOX              00000000000000000000020180872018110        1111  3   X


    My code to parse it is:

    INSERT INTO dbo.tbl_temp_ImpFileImportSecA
               (RecordType
               ,RecordID
               ,Cusip
               ,SecType
               ,SecModifier
               ,SecCalcCode
               ,PrimaryExchange
               ,CpnOrDivRate
               ,MaturityDate
               ,UnderlyingCusip
               ,CallOrStrikePrice
               ,FirstParCallOrUnits
               ,Symbol
               ,CouponFreq
               ,BondClass
               ,FirstCouponDay
               ,CallIndicator
               ,PutIndicator
               ,NextParCallDate
               ,PreRefundedDate
               ,NextPemiumCallDate
               ,DatedOrExDate
               ,FirstCpnOrPayDate
               ,Null1
               ,Null2
               ,IsCNSEligible
               ,IsDTCEligible
               ,IsNSCCEligible
               ,ForeignSecInd
               ,SecondCouponDay
               ,PayMethod
               ,MinorProductCode
               ,RecordEndInd)
    SELECT 
    	NULLIF(LEFT(ImportText,1), '')
    	,NULLIF(SUBSTRING(ImportText,2,6), '')
    	,NULLIF(SUBSTRING(ImportText,8,9), '')
    	,NULLIF(SUBSTRING(ImportText,17,1), '')
    	,NULLIF(SUBSTRING(ImportText,18,1), '')
    	,NULLIF(SUBSTRING(ImportText,19,1), '')
    	,NULLIF(SUBSTRING(ImportText,20,1), '')
    	,CAST(SUBSTRING(ImportText,21,7) as float) / 1000 as CpnOrDivRate
    	,case when SUBSTRING(ImportText,28,7) = '0000000' then NULL
    		else DATEADD(day, cast(SUBSTRING(ImportText,32,3) as int) -1,cast(SUBSTRING(ImportText,28,4) + '0101' as date)) end as MaturityDate
    	,NULLIF(SUBSTRING(ImportText,35,9), '')
    	,CAST(SUBSTRING(ImportText,44,9) as float) / 10000 as CallOrStrikePrice
    	,CAST(SUBSTRING(ImportText,53,9) as float) / 10000 as FirstParCallOrUnits
    	,NULLIF(SUBSTRING(ImportText,62,10), '')
    	,NULLIF(SUBSTRING(ImportText,72,2), '')
    	,NULLIF(SUBSTRING(ImportText,74,1), '')
    	,NULLIF(SUBSTRING(ImportText,75,2), '')
    	,NULLIF(SUBSTRING(ImportText,77,1), '')
    	,NULLIF(SUBSTRING(ImportText,78,1), '')
    	,case when SUBSTRING(ImportText,79,7) = '0000000' then NULL
    		else DATEADD(day, cast(SUBSTRING(ImportText,83,3) as int) -1,cast(SUBSTRING(ImportText,79,4) + '0101' as date)) end as NextParCallDate
    	,case when SUBSTRING(ImportText,86,7) = '0000000' then NULL
    		else DATEADD(day, cast(SUBSTRING(ImportText,90,3) as int) -1,cast(SUBSTRING(ImportText,86,4) + '0101' as date)) end as PrerefundedDate
    	,case when SUBSTRING(ImportText,93,7) = '0000000' then NULL
    		else DATEADD(day, cast(SUBSTRING(ImportText,97,3) as int) -1,cast(SUBSTRING(ImportText,93,4) + '0101' as date)) end as NextPremiumCallDate
    	,case when SUBSTRING(ImportText,100,7) = '0000000' then NULL
    		else DATEADD(day, cast(SUBSTRING(ImportText,104,3) as int) -1,cast(SUBSTRING(ImportText,100,4) + '0101' as date)) end as DatedOrExDate
    	,case when SUBSTRING(ImportText,107,7) = '0000000' then NULL
    		else DATEADD(day, cast(SUBSTRING(ImportText,111,3) as int) -1,cast(SUBSTRING(ImportText,107,4) + '0101' as date)) end as FirstCpnOrPayDate
    	,NULLIF(SUBSTRING(ImportText,114,7), '')
    	,NULLIF(SUBSTRING(ImportText,121,1), '')
    	,NULLIF(SUBSTRING(ImportText,122,1), '')
    	,NULLIF(SUBSTRING(ImportText,123,1), '')
    	,NULLIF(SUBSTRING(ImportText,124,1), '')
    	,NULLIF(SUBSTRING(ImportText,125,1), '')
    	,NULLIF(SUBSTRING(ImportText,126,2), '')
    	,NULLIF(SUBSTRING(ImportText,128,1), '')
    	,NULLIF(SUBSTRING(ImportText,129,3), '')
    	,NULLIF(SUBSTRING(ImportText,132,1), '')
    FROM dbo.tbl_temp_ImpFileImportSec

    Thanks very much in advance for any help or advice!


    • Edited by dkingston Thursday, March 22, 2018 6:10 PM clarity
    Thursday, March 22, 2018 5:20 PM

Answers

  • Ok, I think I've sorted it out. In case anyone else has a similar issue...

    It turns out the incoming date is formatted as a "Zoned Decimal" where the last character represents the final digit and the sign for the whole number as follows.
    Positive Numbers:
    { = 0
    A = 1
    B = 2
    C = 3
    D = 4
    E = 5
    F = 6
    G = 7
    H = 8
    I = 9

    Negative Numbers:
    } = 0
    J = 1
    K = 2
    L = 3
    M = 4
    N = 5
    O = 6
    P = 7
    Q = 8
    R = 9

    So 10{ = +100
    10} = -100
    10E = +105
    10N = -105
    etc.

    This function converts the "Zoned Decimal" to float.

    CREATE FUNCTION [dbo].[udf_ConvertZonedDecimal] 
    (@varcharIN as varchar(255))
    RETURNS float
    AS
    BEGIN
    
    	DECLARE @Result as float
    		,@LastDigit as varchar(1)
    		,@SignInt as Int
    
    	SET @LastDigit = SUBSTRING('01234567890123456789',CHARINDEX(RIGHT(@varcharIN,1),'{ABCDEFGHI}JKLMNOPQR',1),1)
    	SET @SignInt = case when CHARINDEX(RIGHT(@varcharIN,1),'{ABCDEFGHI}JKLMNOPQR',1) > 10 then -1 else 1 end
    	SET @Result = cast(LEFT(@varcharIN,LEN(@varcharIN) - 1) + @LastDigit as float) * @SignInt
    				
    RETURN @Result
    
    END
    GO

    Hope someone finds this helpful.

    • Marked as answer by dkingston Thursday, March 22, 2018 8:46 PM
    Thursday, March 22, 2018 8:39 PM

All replies


  • I'm not able to identify the numeric/decimal values from your data. I cant even see decimal point in it

    Anyways, Use REPLACE to replace the { characters. Then try assigning them to column


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 22, 2018 5:56 PM
  • Hi

    Try this sample with you data:

    DECLARE @ImportText AS varchar(500) = 'A000001B639CJ10810 4000200{0000000         00000000{00000000{BUDFF            00000000000000000000000000000000000          11      X';
    SELECT
           SUBSTRING(@ImportText,21,7) AS [Mytext]
          ,CASE
               WHEN
           TRY_CAST(SUBSTRING(@ImportText,21,7) AS float) IS NULL
                   THEN -1
               ELSE CAST(SUBSTRING(@ImportText,21,7) AS float)
                                                        END AS TryCast;


    Norman

    Thursday, March 22, 2018 6:18 PM
  • So, this is fun, TRY_CAST isn't a built in function on my database. SSMS is 2017 but...
    SELECT @@VERSION returns Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (Intel X86)

    That said, SELECT CAST('000200{' as float) returns the same "Error converting varchar...." error so I'm pretty sure that's where my problem is.

    Thursday, March 22, 2018 6:54 PM
  • TRY_CAST only for SQL2012 or more

    Norman

    Thursday, March 22, 2018 7:07 PM
  • Ok, I think I've sorted it out. In case anyone else has a similar issue...

    It turns out the incoming date is formatted as a "Zoned Decimal" where the last character represents the final digit and the sign for the whole number as follows.
    Positive Numbers:
    { = 0
    A = 1
    B = 2
    C = 3
    D = 4
    E = 5
    F = 6
    G = 7
    H = 8
    I = 9

    Negative Numbers:
    } = 0
    J = 1
    K = 2
    L = 3
    M = 4
    N = 5
    O = 6
    P = 7
    Q = 8
    R = 9

    So 10{ = +100
    10} = -100
    10E = +105
    10N = -105
    etc.

    This function converts the "Zoned Decimal" to float.

    CREATE FUNCTION [dbo].[udf_ConvertZonedDecimal] 
    (@varcharIN as varchar(255))
    RETURNS float
    AS
    BEGIN
    
    	DECLARE @Result as float
    		,@LastDigit as varchar(1)
    		,@SignInt as Int
    
    	SET @LastDigit = SUBSTRING('01234567890123456789',CHARINDEX(RIGHT(@varcharIN,1),'{ABCDEFGHI}JKLMNOPQR',1),1)
    	SET @SignInt = case when CHARINDEX(RIGHT(@varcharIN,1),'{ABCDEFGHI}JKLMNOPQR',1) > 10 then -1 else 1 end
    	SET @Result = cast(LEFT(@varcharIN,LEN(@varcharIN) - 1) + @LastDigit as float) * @SignInt
    				
    RETURN @Result
    
    END
    GO

    Hope someone finds this helpful.

    • Marked as answer by dkingston Thursday, March 22, 2018 8:46 PM
    Thursday, March 22, 2018 8:39 PM