TRIM????? 1

[link9]

It's me again, Margaret.

Is there an equivalent function to TRIM() (from vbScript) in SQL Server?

What it does is takes a string and trims off any leading or trailing spaces in the variable --

i.e. I have some data that was imported from (ACK!) Excel, and when it came in, SQL Server set it up as an nvarchar data type, and so I have all this trailing whitespace on my data --

I went in and changed it to varchar, but it left the spaces in, I guess because it just felt bad truncating my data --

What I would like to say is:

UPDATE tableName SET columnName = TRIM(columnName)

but, of course, SQL Server doesn't seem to like that word.

Thanks!
Paul Prewett

 

[tlbroadbent]

UPDATE tableName SET columnName = RTRIM(columnName)

Use LTRIM for leading spaces. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[link9]

Have I mentioned to you lately that you're the man?

Just set up an account somewhere with EFT capability and give me the number so I can start paying you for all this advice. ;-)

Until next time...

 

[vasah20]

Why not use LTRIM(RTRIM(columnName)) ? I use it in place of the VBS Trim function, and it works perfectly fine.

just a thought.
leo

 

[lahddah]

I am trying to loop through a comma delmited field (peru, ama, ecu,) and perform a function for each of the words between the commas. The loop continues while the field LEN > 0. Before looping after the function, I want to remove everything left of the first comma, including the comma so that when function has been performed for each word, the field will be empty and the loop will end.

I can't seem to find a the correct TRIM function to remove everything left of the comma including the comma.

Can anyone help with this?

Thanks!


~ lahddah

 

[ESquared]

lahddah, take a look at

Passing a list of values to a Stored Procedure (Part II) faq183-5207

For a function which does almost exactly what you're asking for.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[SQLBill]

FYI Lahddah,

You really should have started your own thread on this subject and not added to a thread that is 4 years old.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

Good call, SQLBill. I shouldn't have answered.