Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: Trim equivalent..

    I am looking for an equivalent function to trim.

    Right now I'm using

    LTrim(RTrim(@MyStringVar))

    to do the same thing but if there is a better way i'm willing to try it. I'm not currently connected to the SQL server here but can anybody tell me if

    CAST @MyStringVar VarChar

    will trim both leading and trailing spaces?

  2. #2
    Join Date
    Sep 2002
    Posts
    53

    Re: Trim equivalent..

    Hello Access_Dude!

    Here is something I do alot when I find that I am going to be replicating a task. I create a function that does the task that I will be repeating. In your case it would be something like this:

    CREATE FUNCTION dbo.Trim (@tempString varchar(8000))
    RETURNS varchar(8000)
    AS
    BEGIN
    IF @tempString IS NOT NULL
    IF LEN(@tempString) > 0
    RETURN (LTRIM(RTRIM(@tempString)))
    END

    To test this just go to query analyzer and type this:
    DECLARE @T1 CHAR(10)
    DECLARE @T2 VARCHAR(10)
    SET @T1 = ' test ' -- 3 leading spaces, test, and 3 trailing spaces
    PRINT DATALENGTH(@T1)
    PRINT @T1
    SET @T2 = dbo.trim(@T1)
    PRINT DATALENGTH(@T2)
    PRINT@T2

    You should see the first string reads 10 characters long and prints with all 10 characters. The second set of statements should read 4 characters long and print with only 4 characters.

    This can also be done directly in your SELECT statements, such as:
    SELECT dbo.Trim(fieldname) FROM tablename

    When done from the SELECT statement implicit conversion takes places so you don't have to cast or convert these values to anything other than what they are.

    HTH!
    KDK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •