TRIM$() problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TRIM$() problem

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-17 : 01:48:41

I have this problem...

insert into table1(ID, fEntry)
select 'A125487', (nLName + nFirstName + nMI + nMobileNo + nMessages) FROM DataEntry

Result of the select statement is... (note: underscore is represented by spaces.)

'A125487', DelaCruz______________________________Juan Tamad_______________________C._______________________091645879887 _____________________________________ Hello There

I could save this data however it confuses me that sometimes the second field returns null after insertion. however i already uses ltrim and rtrim functions on the fields. Any idea what causes this? and any solutions?

someone told me that setting ANSI_PADDING will do it? How will i set it?

Want Philippines to become 1st World COuntry? Go for World War 3...

B0g
Starting Member

19 Posts

Posted - 2004-12-17 : 01:56:11

Have you tried this one:

insert into table1(ID, fEntry)
select 'A125487'
, (RTRIM(nLName) + ' ' + RTRIM(nFirstName) + ' ' + RTRIM(nMI) + ' ' + RTRIM(nMobileNo) + ' ' + RTRIM(nMessages))
FROM DataEntry

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-17 : 01:57:13

i already tried it however in some instances i inserted a null value on it!

Want Philippines to become 1st World COuntry? Go for World War 3...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-17 : 02:05:15

Are your data types CHAR versus VARCHAR? The CHAR type will pad with blanks in order to fill up to the maximum size. That could explain the spacing.

By default, concatenating a string with a Null value results in a Null (i.e., 'ABC' + Null = Null). If any of the columns are Null, the resulting value is Null.

Here are a couple of ways to adjust for this:

1) Use COALESCE(ColumnName, '') to convert any Nulls to empty strings.
2) There is a SET option that you can alter to define how to handle concaterating Nulls. I don't have the exact name available but it something like CONCAT_NULL_YIELDS_NULL. Look up SET options in Books Online for the specifics.

HTH

=================================================================

Happy Holidays!

B0g
Starting Member

19 Posts

Posted - 2004-12-17 : 02:07:45

Probably some of the fiels are NULL and the "+" operator goes like this: 'a' + NULL = NULL so you should use ISNULL()

insert into table1(ID, fEntry)
select 'A125487'
, (ISNULL(RTRIM(nLName), '') + ' ' + ISNULL(RTRIM(nFirstName), '') + ' ' + ISNULL(RTRIM(nMI), '') + ' ' + ISNULL(RTRIM(nMobileNo), '') + ' ' + ISNULL(RTRIM(nMessages), ''))
FROM DataEntry

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-17 : 02:08:19

Jonas,

just to check something, check:

select count(*)
FROM DataEntry
where
nLName is null
or nFirstName is null
or nMI is null
or nMobileNo is null
or nMessages is null

Alternatively, check where they are ALL null (and's).

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-17 : 02:10:54

So thats the reason: some fields are null

I've try...
select null + 'aida'

and it returns me a null value...

thanks Bustaz Kool and BOg..

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources