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

Question

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

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

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

ajashadi Ten Centuries Points: 1017 More actions · Answer 1

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???

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

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"

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

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)

ajashadi Ten Centuries Points: 1017 More actions · Answer 4

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..

SQLBill SSC Guru Points: 51440 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

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)