Post reply

String Manipulatiom

  • August 10, 2010 at 10:02 am

    #225274

    Can anyone help me on following string manipulation?

    Basically i want to trucate the digits before decimal point and select rest of the part with decimal.

    I have the query as follows:

    SELECT SALESTABLE.salesid,SALESTABLE.MODIFIEDTIME, SALESTABLE.MODIFIEDTIME/60/60 as hour,

    (CAST(SALESTABLE.MODIFIEDTIME AS float)/3600)

    FROM SALESTABLE

    where (SALESTABLE.SALESID = N'SO1000863')

    select .8061111111111*60 // Need to be able to do this in above query dynamically

    Result1:

    salesid MODIFIEDTIMEhour(No column name)

    SO1000863389021010.8061111111111

    Result2:

    48.3666666666660

  • August 10, 2010 at 10:12 am

    #1204890

    is it really string manipulation?

    if it's a float/decimal, you could subtract the FLOOR() of the value from itself to get the remainder:

    declare @val float

    set @val = 10.8061111111111

    SELECT @val - floor(@val)--returns 0.8061111111111

    --if it's a string?!?!

    declare @val2 varchar(30)

    set @val2 = '10.8061111111111'

    SELECT convert(float,@val2) - floor(convert(float,@val2))--returns 0.8061111111111

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 10, 2010 at 10:15 am

    #1204892

    DECLARE @test-2 AS DECIMAL(19,11)

    SET @test-2 = 55.32178699281

    SELECT @test-2

    ,SUBSTRING(CONVERT(VARCHAR(MAX),@TEST),CHARINDEX('.',@TEST),LEN(@TEST))

    ,CONVERT(DECIMAL(19,11),SUBSTRING(CONVERT(VARCHAR(MAX),@TEST),CHARINDEX('.',@TEST),LEN(@TEST)))*60

    /*

    55.32178699281.3217869928119.30721956860

    */

    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 10:21 am

    #1204895

    Thanks Lowell.

    Floor worked for me.

    SELECT SALESTABLE.salesid,SALESTABLE.MODIFIEDTIME, SALESTABLE.MODIFIEDTIME/60/60 as hour,

    ((CAST(SALESTABLE.MODIFIEDTIME AS float)/3600) -floor((CAST(SALESTABLE.MODIFIEDTIME AS float)/3600)))*60

    FROM SALESTABLE

    where (SALESTABLE.SALESID = N'SO1000863')

    Result

    salesidMODIFIEDTIMEhourminute

    SO1000863389021048.3666666666667

  • August 10, 2010 at 10:22 am

    #1204896

    Skcadavre,

    Thanks for your reply

Viewing 5 posts - 1 through 4 (of 4 total)

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