Post reply

Format varchar value to numeric

  • August 10, 2010 at 12:32 am

    #225907

    Hi ,

    I have ar Tarief column in temp table where data comes through SSIS .I want to insert this data in actual table with format like 12.34 ( actual data is like 1234).

    Last two nos should be decimal. The datatype of the column is numeric(10,2).

    I have created this query

    SELECT code,'1999-01-01',Tarief,

    SUBSTRING(Convert(Varchar,Tarief),1,Len(Tarief)-2),

    SUBSTRING(Convert(Varchar,Tarief),Len(Tarief)-1,Len(Tarief))

    FROM Temp_CBVTarieven

    but it is giving the message

    " Invalid length parameter passed to the LEFT or SUBSTRING function."

    The data is in plane numbers , and upto 10 digits .

    Can anybody help.

    Thanks

  • August 10, 2010 at 1:25 am

    #1204519

    Howdy,

    try this:

    declare @i int

    set @i=1234567890

    select left(@i,len(@i-2))+'.'+right(@i,2)

  • August 10, 2010 at 1:30 am

    #1204521

    HI ,

    It is replicating last two nos.

    I dont want it like this .

    I want decimal before last two nos .

    Thanks

  • August 10, 2010 at 1:33 am

    #1204522

    sorry had a spell check issue...

    try now

    declare @i int

    set @i=1234567890

    select left(@i,len(@i)-2)+'.'+right(@i,2)

  • August 10, 2010 at 1:42 am

    #1204524

    HI ,

    I tried this ..

    select tarief ,left(Tarief,len(Tarief)-2)+'.'+right(Tarief,2) from Temp

    Its giving result as well as msg as

    "Invalid length parameter passed to the LEFT or SUBSTRING function."

    Whats wrong with this ?

  • August 10, 2010 at 1:46 am

    #1204527

    post some sample data and table structures

  • August 10, 2010 at 1:52 am

    #1204531

    Here it is

  • August 10, 2010 at 2:02 am

    #1204536

    something slighty different

    select cast(tarief as decimal(10,2))/100

  • August 10, 2010 at 2:12 am

    #1204541

    Aspg (8/10/2010)

    Here it is

    First of all, a complaint.

    When you get asked to post sample data, I should be able to copy it into my query window and execute to get a usable table, like so.

    DECLARE @TABLE AS TABLE(

    [Tarief] numeric (10,0) NULL)

    INSERT INTO @TABLE

    SELECT 1270

    UNION ALL SELECT 980

    UNION ALL SELECT 991

    UNION ALL SELECT 1170

    UNION ALL SELECT 1200

    UNION ALL SELECT 17470

    UNION ALL SELECT 17700

    Second -

    Aspg (8/10/2010)

    HI ,

    I tried this ..

    select tarief ,left(Tarief,len(Tarief)-2)+'.'+right(Tarief,2) from Temp

    Its giving result as well as msg as

    "Invalid length parameter passed to the LEFT or SUBSTRING function."

    Whats wrong with this ?

    The below would work with my sample table: -

    SELECT LEFT([Tarief], Len([Tarief]) - 2) + '.' + RIGHT([Tarief], 2)

    FROM @TABLE

    As would this: -

    SELECT CAST(CAST([Tarief] AS DECIMAL(11, 2)) / 100 AS DECIMAL(6, 2))

    FROM @TABLE

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 10, 2010 at 2:25 am

    #1204549

    mega solution 🙂

    SELECT [Tarief], [Tarief2]= cast(0.01*[Tarief] as varchar) FROM @TABLE

  • August 10, 2010 at 2:28 am

    #1204551

    Would this help you ?

    declare @i int

    set @i = 1234567890

    select cast( (@i / 100.00) as numeric(10,2))

  • August 10, 2010 at 2:32 am

    #1204552

    And if your input is a string (varchar/char/nvarchar) , you can do this :

    declare @i varchar(10)

    set @i = '1234567890'

    select stuff(@i, (len(@i) - 1), 0 , '.' )

    Hope this helps!!

  • August 10, 2010 at 2:37 am

    #1204554

    Thanks a lot .

    Solved my problem

    🙂

  • August 10, 2010 at 2:39 am

    #1204557

    Aspg (8/10/2010)

    Thanks a lot .

    Solved my problem

    🙂

    Awesome 🙂 ... which solution u used, mate ?

Viewing 14 posts - 1 through 13 (of 13 total)

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