Issue in executing SQL Query with Trim, Mid and Len functions

Question

4.00/5 (2 votes)

See more:

SQL-Server-2008

Dear All,

I have written the following Query that works fine with access database:

Update MyTable Set [Column New]=IIf(Right(Trim([Column New]),1)=';',Mid([Column New],1,Len([Column New])-1),[Column New])

But, when I execute the same in SQL Server 2008, It says:

'Trim' is not a recognized built-in function name.

How Do I write the same in SQL Server ?

Any help or suggestion would be greatly appreciated.

Regards,
Raj

Posted 11-Sep-11 20:37pm

Raj.rcr

Add a Solution

Comments

Raj.rcr 13-Sep-11 2:54am

Thank you Abhinav and Simon... The following is the query that is working fine :

UPDATE MyTable SET [Column New] = CASE WHEN RIGHT(LTRIM(RTRIM([Column New])),1) = ';' THEN SUBSTRING([Column New], 1, LEN([Column New])-1) ELSE [Column New] END

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2011-09-11T20:42:00

Solution 1

SQL Server does not have a TRIM function. It has LTRIM and RTRIM that you can use together. There is no MID or RIGHT as well. You need to use SUBSTRING instead[^].

Posted 11-Sep-11 20:42pm

Abhinav S

Comments

Mehdi Gholam 12-Sep-11 2:44am

My 5!

Abhinav S 12-Sep-11 2:47am

Thank you Mehdi.

Raj.rcr 12-Sep-11 2:46am

But there's a issue with IIf also... What can I use for IIf?

Abhinav S 12-Sep-11 2:48am

SQL Server provides support for an IF...Else block.
As additional info, you could also use CASE.

Simon Bang Terkildsen 12-Sep-11 2:53am

Or wait for SQL Server Denali as it adds support for IIF

Simon Bang Terkildsen 12-Sep-11 2:48am

+5As I'va stated in my answer you can also make your own TRIM function.

Pravin Patil, Mumbai 12-Sep-11 2:54am

Nice answer. My 5

Simon Bang Terkildsen · Accepted Answer · 2011-09-11T20:45:00

Solution 2

As you've now found out T-SQL doesn't have a TRIM function, you can use RTRIM and LTRIM instead, e.g. RTRIM(LTRIM([Column New]))

you can also make your own TRIM function

SQL

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX)) RETURNS VARCHAR(MAX)
BEGIN
    RETURN RTRIM(LTRIM(@string))
END

Posted 11-Sep-11 20:45pm

Simon Bang Terkildsen

Updated 11-Sep-11 20:46pm

v2

Comments

Mehdi Gholam 12-Sep-11 2:47am

My 5!

Simon Bang Terkildsen 12-Sep-11 2:49am

Thank you, Mehdi

Raj.rcr 12-Sep-11 2:47am

But there's an issue with IIf also... What can I use for IIf?

Abhinav S 12-Sep-11 2:49am

5 for building your own function.

Pravin Patil, Mumbai 12-Sep-11 2:55am

My 5 too for the function...