Post reply

ltrim(rtrim(colvalue)) doesnt work while inserting/displaying records into a table from another table...

  • December 21, 2006 at 5:04 pm

    #115542

    Hi,

    I have some data in holding table. I use bulk insert process to insert records into the holding table. Now the holding table has some records which have extra spaces after string or just empty spaces or maybe tabs. I have to insert records from holding table to main table. How Do I remove the spaces or extra tabs or left spaces or right spaces??? This is very important and I need this solution ASAP.

    I tried ltrim(rtrim(colvalue)) while inserting records but it doesnt work in sql server 2000. Once records are inserted in table, I am not able to trim the records.. Do you know any sql server 2000 fix for removing extra spaces left or right or remove extra spaces for empty values which are sent with empty tabs or spaces???

    I even tried to display data by using ltrim(rtrim(colvalue)) which has spaces, but the results were not shown with trimmed data, they showed up with spaces and tabs.. I am i missing something here???

    Thanks in advance..

  • December 21, 2006 at 5:20 pm

    #679618

    ok, I think ltrim(rtrim(colvalue)) can remove spaces but cannot remove empty values that just have tabs or even a value which has a tab on the left or right side of the string... Any suggestions???

  • December 21, 2006 at 9:41 pm

    #679629

    hi

    i think the replace function will work here.

    ex: i have a string value "value1" and a tab after that which looks like this

    "value1     ".  using replace

    SELECT REPLACE (value1     '  ,'     ','') .....

    "Keep Trying"

  • December 21, 2006 at 9:59 pm

    #679634

    A slightly easier to see method... if "Value1" is a column name....

    SELECT REPLACE(Value1,CHAR(9),'') AS Value1

      FROM your table

    "CHAR(9)" is the TAB character.

    HOWEVER!!!!  If you have such characters in your data holding table after the BCP import, you may have designed the BCP format file incorrectly.  If you are not using a format file, that may actually be a large part of the problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 22, 2006 at 7:50 am

    #679722

    bcp testjan11..ws_initiate_session format -c

    -f C:\Documents and Settings\amohammed\My Documents\ajas.fmt -T

    I remember it asks for field termination and i used the tab option. So my .fmt file is like this... first few columsn from .fmt file..........

    we accept tab delimited files.

    8.0

    84

    1 SQLCHAR 0 20 "\t" 1 client_id SQL_Latin1_General_CP1_CI_AS

    2 SQLCHAR 0 20 "\t" 2 employee_co_id SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 0 50 "\t" 3 employee_last SQL_Latin1_General_CP1_CI_AS

    4 SQLCHAR 0 50 "\t" 4 employee_first SQL_Latin1_General_CP1_CI_AS

    5 SQLCHAR 0 10 "\t" 5 hire_date ""

    6 SQLCHAR 0 2 "\t" 6 work_state SQL_Latin1_General_CP1_CI_AS

    can someone suggest something..

    i need solution asap...thanks all..

  • December 22, 2006 at 8:23 am

    #679737

    Two other issues:

    1. How are you VIEWING the data? If you are using Query Analyzer, on the menu bar there is Tools. Go there and select Options. Go to the Results tab. There is a setting for the number of characters shown, Query Analyzer will use that for almost every datatype.

    2. What datatype is the column? If it is CHAR, then the length is static. For example: if the column is CHAR(50) and you put 10 characters into it, it will return the ten characters and 40 blanks.

    -SQLBill

  • December 22, 2006 at 9:34 am

    #679760

    I agree with SQLBill on many of the points he made.  You might just be viewing things differently or incorrectly... post the CREATE TABLE statement for the target table... if you are using CHAR for columns, that could be the "problem"...

    To find out if you actually have tabs stored in columns, do this...

    SELECT *
    FROM yourtable
    WHERE somecolumnname LIKE '%'+CHAR(9)+'%'

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

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