What is SQL speak for Trim(string)

[LittleSmudge]

I'm an MSAccess developer but I need to get data from SQL Server database.

The current problem I'm having is that a fields have been defined as Char rather than VarChar so they have unwanted trailing space characters.

So I need to do something like

"SELECT Trim(Field1) & Trim(Field2) As F12 FROM tblName"

but I get an error message saying SQL does not recognise Trim

So what's the SQL speak for Trim ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[johnherman]

RTRIM and LTRIM for right and left trim of a string.

-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[JayKusch]

SELECT RTrim(LTRIM((Field1)) & RTrim(LTRIM((Field2)) As F12 FROM tblName"


Thanks

J. Kusch

 

[SQLSister]

RTrim trims trailing right spaces, LTrim trims spaces on the left.

Questions about posting. See faq183-874

 

[LittleSmudge]

Thanks folks.

So in something as complex as SQL server is there really nothing that does something as basic as both LTrim and RTrim at the same time !


Much appreciate the swift responses.

G.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[AtomicWedgie]

maybe replace(field1 + field2,' ','')

I believe you also have to replace the & with a + in SQL


Regards,

Atomic Wedgie

 

[SQLSister]

LIttleSmudge - you will see that alot as you move up from Access. The complexity level in SQL Server is different because the programmers of the software felt that at this level of database there was a need for more control. More control equals more complexity. More than likely Access does the equivalent of ltrim and rtrim, too; it just shields you from having to know about it.

Questions about posting. See faq183-874

 

[ESquared]

Access/VB offers LTrim and RTrim, too. It just gives you a shortcut in Trim.

As for concatenating, you can use + or & in VB. The difference is that + propagates nulls and & treats them as empty strings. So, the + behavior in SQL server is identical to the VB + behavior.

 

[johnherman]

Oracle 9i and later offers TRIM, but earlier versions only have RTRIM and LTRIM as do all versions of SQL Server.

-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[LittleSmudge]

So, the + behavior in SQL server is identical to the VB + behavior

I'd disagree with you there Esquared because the string + string can be VERY dangerous in VB because VB tries to be accomodating in Type translations.

Example - in VB speak

Dim strA As String
dim strB As String
strA = "56"
strB = "56B"

I think most people are happy that
Debug.Print strA & 47
will print "5647"

However:-
Debug.Print strA + 47
will print 103

worse still

Debug.Print strB & 47
will print "56B47"

Whereas
Debug.Print strB + 47
will cause a run time crash.


I don't believe SQL works in this peculiar manner - does it ?

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LFI]

Have you tried this:

select trim(both ' ' from ' ball ') from dual

This works for us with Oracle 8i (Version 8.1.7.4.1, I think).

--Dave

 

[ESquared]

LittleSmudge,

Thanks for pointing out the inconsistencies in VB's 'helpful' behavior in automatic type conversion. Some of those are pretty lame.

Any programmer who 'adds' or 'concatenates' a number literal and expects string output should be fired, even if VB happens to make it work. That's what CStr and Format are for.

Last, when I said,

So, the + behavior in SQL server is identical to the VB + behavior

I was referring to the propagation of nulls only.

 

[LittleSmudge]

Okay Esquared.

The 'helpful'ness ( as you put it ) of VB is why you'll find VB coders avoid the use of + and why I was very reluctant to try it.


And because MSAccess is plagued with the image that any fool can develop applications with it I spend most of my life ( I get most of my work from ) fixing other people's cr@p attempts.

I therefore get to see all sorts of interesting bits of code !!!








G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.