Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: Making a String shorter

    Hi guys,

    This is my problem I have a table to which data is being imported via bcp. When I get this data there are some fields nvarchar that has some decimal, for instance 1098.000000 is always 6 zeros

    What I want to achieve is to make the above number only 1098

    So what I did was this:
    LEFT (AMOUNT, LEN(AMOUNT)-7) AS AMOUNT

    But i get an error saying invalid lenght parameter passed to the left or substring

    Any ideas?

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why are you storing a number as an NVARCHAR, and why do you care about the trailing zeros? This is a symptom of design problems that will haunt you!

    The code to "fix" this issue is simple, but while it will fix this problem you really need to address the underlying problem instead of the text formatting.
    Code:
    CREATE TABLE #foo (
       AMOUNT	NVARCHAR(50)	NOT NULL
       )
    
    
    INSERT INTO #foo (AMOUNT)
       VALUES ('1098.000000')
    
    SELECT * FROM #foo
    
    ALTER TABLE #foo
       ALTER COLUMN AMOUNT NUMERIC (10, 0)
    
    SELECT * FROM #foo
    
    DROP TABLE #foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •