stripping decimal data from an nvarchar value...

Hot Topic

SQL Server July Books Online access issue
Stored Procedure with Select, how to use it with data reader?
Monitoring a folder for a file - start ssis
ODBC as Data Source in Workgroup Edition
Delete files older than 3 days using SSIS tasks
fn_virtualfilestats doesn't work
SSIS package execution error
SSIS package refresh
error when processing cubes just migrated from 2000 to 2005
Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005?

SQL Server

Use dataset content in expressions
Databound Image to header...export issue
Header on Matrix Rows
Deleting, Updating and Inserting---is it LOCKING the table?
SQL Server Express Edition silently at command prompt
How to enable Remote Erros on Report Server 2005?
Convert DT_14 to DT_18
Hierarchy Equivalent of a Crossjoin
SQLCMD to execute all script files in directory?
SQL Server 2000 Connects to Remote DB but not Local DB. Why?

stripping decimal data from an nvarchar value...

I have a table that contains an nvarchar column of data. The data is actually a monetary value; sometimes with a decimal point sometimes without.

My problem is that I need to strip the decimal portion of the string if it exists. From a select statement I can use:

SUBSTRING(DW_OBP_ORD_TMP_IC.VALUE,1,LEN(VALUE)-(LEN(VALUE)-CHARINDEX('.',DW_OBP_ORD_TMP_IC.VALUE)

if a decimal point exists. But if one does not the CharIndex comes back 0 and my equation does not work correctly.

I need to pull the data from the table, along with many other fields. How do I do this without using a cursor I have millions of rows so need a solution that will be quick.

thanks in advance,

Marilyn

Answer this question

stripping decimal data from an nvarchar value...

  • TA123

    Marilyn:

    If you ever have to worry about whether or not the number is formatted correctly:

    set nocount on

    declare @mockUp table (aValue nvarchar (20) not null)
    insert into @mockUp values ('+2E5')
    insert into @mockUp values ('-1.45001D2')
    insert into @mockUp values ('45')
    insert into @mockUp values ('127.14')
    insert into @mockup values ('+61.47')
    insert into @mockup values ('-461.')
    insert into @mockup values ('Bad Value')

    -- ---------------------------------------------------------
    -- The advantage of the case statement is that if the
    -- data is an invalid value that it does not cause an
    -- execution error.
    --
    -- Try un-commenting either of the converts and you will
    -- get an execution error similar to this:
    --
    --
    -- Server: Msg 8114, Level 16, State 5, Line 21
    -- Error converting data type nvarchar to float.
    --
    --
    -- The case statement makes the conversion more bullet
    -- proof.
    -- ---------------------------------------------------------
    select aValue,
    isNumeric (aValue) as [isNumeric],
    -- convert (float, aValue) [Float Value],
    -- convert (int, convert(float, aValue) ) [Int Value],
    case when isNumeric (aValue) = 1
    then convert (int, convert(float, aValue))
    else null
    end as convertedValue
    from @mockup

    -- ---------------------------------------------
    -- Sample Output:
    -- ---------------------------------------------

    -- aValue isNumeric convertedValue
    -- -------------------- ----------- --------------
    -- +2E5 1 200000
    -- -1.45001D2 1 -145
    -- 45 1 45
    -- 127.14 1 127
    -- +61.47 1 61
    -- -461. 1 -461
    -- Bad Value 0 NULL

  • GJVH

    select cast(cast(value as float)as int) from tablename



  • donkaiser

    Nitin Khurana wrote:

    select cast(cast(value as float)as int) from tablename



    That will work as well.


  • KevinRevenueResearch

    Convert the field to numeric, then convert to int. That is if you can ensure that all of the data is monetary.

    Examples:
    select '45.64' as col1, cast(cast('45.64' as numeric(4,2)) as int) as col2
    select '34' as col1, cast(cast('34' as numeric(4,2)) as int) as col2

  • stripping decimal data from an nvarchar value...
Answer this question