SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL Server 2005 does not have that either. I have created very simple UDF which does the same work.

FOR SQL SERVER 2000:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO

FOR SQL SERVER 2005:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO

Both the above UDF can be tested with following script
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'

Here is the quick video on the same subject:

There will be no spaces around them. It is very little but useful trick.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

127 thoughts on “SQL SERVER – TRIM() Function – UDF TRIM()

  1. Pingback: SQL SERVER - SPACE Function Example Journey to SQL Authority with Pinal Dave

  2. Can I rtrim using a delimiter? I would like to trim the last characters following a “.”. Example 10.11.21.386 result to be 10.11.21

    Like

    • It is becuase when you use Varchar datatype, by default spaces on the right are omitted. For Char datatype it means length is fixed and no need to trim it until needed.

      What if the use wants to trim only at the one end?
      Thats why you have seperate functions

      Like

  3. Is there any way to get this function in the sys schema?

    CREATE FUNCTION sys.TRIM(@string VARCHAR(8000))
    RETURNS VARCHAR(8000)
    BEGIN
    RETURN LTRIM(RTRIM(@string))
    END
    GO

    We’ve got a application that runs to an informix database
    where all the users credentials are placed in.
    When I run the application on the SQL Server 2005 database, it returns an error that says
    ‘TRIM’ is not a recognized built-in function name

    I’m not able to change the application……

    Like

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

    CMIIW, this function cant return a really 8000 char does it ?
    or even 257 char ? right ?

    because the sql2k will truncate the result varchar into 256 char

    anyone knows about solving this? or it’s just my weird sql 2k problem?

    Like

  5. @erik,
    I would prefer this function to be added in sys schema.

    @Saru,
    Any example of your ascii chars?

    @Cristian,
    You will have to use replace function?

    @Peter Parker,
    SQL Server 2005 returns varchar(max) sucessfully.

    @ree,
    not sure what you are asking, If you can explain your problem little more.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

    Like

    • hi,
      i use this trim function but still SProc showing the error
      “String or binary data would be truncated.”
      so can u tell me why this happen?
      scenario :- i m inserting records from 1 DB to another.

      Like

    • it probably ‘doesnt work’ in your case because CHAR is a fixed length field, so if you trim:
      ‘Sample ‘

      it becomes:
      ‘Sample’

      but when you save it to a char(8) field, it becomes:
      ‘Sample ‘

      because it is padded with spaces again.

      Like

  6. I have one problem….

    In the query i am getting the values like(‘13,14,15′) so in that i am searching the data related to 13,14,15 so i have to trim the ‘ ‘ and finally i want the values like (13,14,15) so for this can u give any idea how to remove those ‘ ‘

    Like

  7. Hi

    I am attempting to migrate code from Oracle to SqlServer 2005

    Why does select rtrim(ltrim(‘ ‘)) not return null in Sqlserver ? Oracle does retrun a null

    I also tested via your trim script

    select isnull(dbo.trim(‘ ‘),’Y’) – returns ” not ‘Y’

    What am I missing ?

    Thanks

    Like

  8. Hi All

    I have looked every where to find my answer. May bee someone can help me here :)

    I have an entry I need to trim. I looks like this:

    “0114503401”0114503401<sip:014503401@14.208.19.114/5060_ID_ASDEWSDERFDE

    The length of characters before <sip: are not the same.
    The length of characters after @ are not the same.

    I need to trim everything after the @ and everything before the <sip:.

    My result needs to be 0114503401.

    How now?

    Thanx in advance.

    :)

    Like

  9. Hi ,

    I tried adding the Trim function in sys schema.

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

    i get the following error:

    Msg 2760, Level 16, State 1, Procedure TRIM, Line 4
    The specified schema name “sys” either does not exist or you do not have permission to use it.

    i am using a database where my id is the dbo.owner. i belive this should not be an authentication issue..

    Like

  10. Sorry guys, but I find that trim function a little lame. All it does is save a handful of keystrokes. Here’s mine – it removes all whitespace, including tabs, carriage returns and line feeds. Further, it can be easily modified to trim any set of characters – just make @charstotrim a parameter.

    ALTER function dbo.trim(@input varchar(8000)) returns varchar(8000) as
    begin
    declare @charstotrim varchar(100)
    select @charstotrim =’ ‘+char(9)+char(10)+char(13)
    while charindex(left(reverse(@input),1),@charstotrim) >0
    select @input=left(@input,datalength(@input)-1)
    while charindex(left(@input,1),@charstotrim) >0
    select @input=substring(@input,2,datalength(@input)-1)
    return @input
    end

    Like

  11. hi,

    this is my first request to u.

    I want to retrieve n capital characters from a field in sqlserver 2005.
    Eg: ‘Lake Systems’-is the value in field
    Desired Output-‘LS’

    Like

  12. I’ve created my own trim and concat functions but I can’t call them with the dbo. because the same software works with DB2 and Oracle.
    Any ideas…
    Thanks

    Like

  13. if i delete the record of 5th in product table,but not change the productid value,Productid value maintain 1,2,3,4,6..but i waunt 1,2,3,4,5… change of productid,Productid is the itentity

    Like

  14. Hi,

    I have an issue where I am not able to enter the data into the database during update statement.

    The string datatype is nchar varying(127) so it seems to extend more than this and so its not updating the table.

    I need a Trim function which will give me the first 126 chars of the string and then remove everything else, so that it will be updated in the table.

    Thanks,
    Lavanya.

    Like

  15. I am trying to remove leading and trailing spaces in all columns in the database. Following is the query I am using, but an error Msg 1087, Level 15, State 2, Line 38
    Must declare the table variable “@Tables”. Although the variable is declared.

    The Query:

    declare @Total_Columns int
    declare @Columns Varchar(256)
    Set @Columns = Null
    Declare Sam_Cursor_Clm Cursor for
    Select c.name from sys.columns c
    inner join sys.objects t on c.object_id = t.object_id
    where t.type = ‘u’
    and t.name ‘sysdiagrams’

    Open Sam_Cursor_Clm

    Fetch Next from Sam_Cursor_Clm
    Into @Columns

    declare @Tables Varchar(256)
    Set @Tables = Null
    Declare Sam_Cursor_Tbl Cursor for
    Select t.name from sys.columns c
    inner join sys.objects t on c.object_id = t.object_id
    where t.type = ‘u’
    and t.name ‘sysdiagrams’
    Open Sam_Cursor_Tbl

    Fetch Next from Sam_Cursor_Tbl
    Into @Tables

    Set @Total_Columns = 0

    while @Total_Columns < (Select count(c.name) from sys.columns c
    inner join sys.objects t on c.object_id = t.object_id
    where t.type = ‘u’
    and t.name ‘sysdiagrams’
    )
    Begin
    Set @Total_Columns = @Total_Columns + 1
    UPDATE @Tables
    SET @Columns = LTRIM(RTRIM(@Columns))

    Fetch Next from Sam_Cursor_Tbl
    Into @Tables

    Fetch Next from Sam_Cursor_Clm
    Into @Columns

    End
    Close Sam_Cursor_Tbl
    Deallocate Sam_Cursor_Tbl
    Close Sam_Cursor_Clm
    Deallocate Sam_Cursor_Clm

    Please Help!

    Like

  16. Sorry Russ, but you can do better than that:

    create function dbo.LTrimX(@str varchar(8000)) returns varchar(8000)
    as
    begin
    declare @trimchars varchar(10)
    set @trimchars = char(9)+char(10)+char(13)+char(32)
    if @str like ‘[‘ + @trimchars + ‘]%’ set @str = substring(@str, Patindex(‘%[^’ + @trimchars + ‘]%’, @str), 8000)
    return @str
    end
    go

    create function dbo.RTrimX(@str varchar(8000)) returns varchar(8000)
    as
    begin
    declare @trimchars varchar(10)
    set @trimchars = char(9)+char(10)+char(13)+char(32)
    if @str like ‘%[‘ + @trimchars + ‘]’
    set @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
    return @str
    end
    go

    create function dbo.TrimX(@str varchar(8000)) returns varchar(8000)
    as
    begin
    return dbo.LTrimX(dbo.RTrimX(@str))
    end
    go

    Like

  17. Sorry Russ, but I prefer the following implementation:

    create function dbo.LTrimX(@str varchar(8000)) returns varchar(8000)
    as
    begin
    declare @trimchars varchar(10)
    set @trimchars = char(9)+char(10)+char(13)+char(32)
    if @str like ‘[‘ + @trimchars + ‘]%’ set @str = substring(@str, Patindex(‘%[^’ + @trimchars + ‘]%’, @str), 8000)
    return @str
    end
    go

    create function dbo.RTrimX(@str varchar(8000)) returns varchar(8000)
    as
    begin
    declare @trimchars varchar(10)
    set @trimchars = char(9)+char(10)+char(13)+char(32)
    if @str like ‘%[‘ + @trimchars + ‘]’
    set @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
    return @str
    end
    go

    create function dbo.TrimX(@str varchar(8000)) returns varchar(8000)
    as
    begin
    return dbo.LTrimX(dbo.RTrimX(@str))
    end
    go

    Like

  18. Pingback: SQL SERVER - 2008 - TRIM() Function - User Defined Function Journey to SQL Authority with Pinal Dave

  19. I have a CSV file in which each record gives the name of PDF files which look something like this:

    “FS-EST-008_0678951-01_janedoe_ab93fd78.pdf”

    I have created a DTS to make the _ the delimiter and the text qualifier is set to “none”. The result is 4 columns in my SQL table. I am only using the first two columns and have added a 3rd column to include the [Date of Import]. The table is appended daily and I am executing a SQL Task as follows:

    Update [DCSSFTPData].[dbo].[PD-Q058-ALT-LBP]

    Set [Date of Import] = cast(getdate() as varchar(12))
    where [date of import] is null

    Then executing a separate SQL Task as follows:

    delete from [DCSSFTPData].[dbo].[PD-Q058-ALT-LBP]
    where DATEDIFF(day, [date of import], GETDATE()) > 90

    All is well except……How do I get rid of those pesky double quotes at the beginning and end of each record? I’ve tried MID, REPLACE, TRIM, and I must not be using the right syntax for any of them.

    I’ve only been working with SQL for 4 months, with no training, so I’m flying by the seat of my pants and could sure use some expert assistance! THANK YOU SOOOO MUCH!

    Laurie

    Like

  20. Laurie –

    You are mixing and matching. You cannot use _ as a delimiter and also have it recognize the double quotes as text qualifier. Either one or the other, as you can’t break up a column in the middle of your text qualifiers.

    What I’d do… is pull in the data in its existing format and manipulate it as needed afterwards.

    1. Use the double quotes as text qualifier, and pull the string into one column on your import, say into column called import_str.

    2. Then add two columns to the table created in the import process, and rip apart your string as needed.

    Something like this…

    ALTER TABLE importTable add rip_out_str1 varchar(255)
    ALTER TABLE importTable add rip_out_str2 varchar(255)

    — extract first col and remove extracted str from raw data
    UPDATE importTable
    SET rip_out_str1 = SUBSTRING(import_str,0,CHARINDEX(‘_’, import_str)),
    import_str = replace(import_str, rip_out_str1+’_’, ”)

    — extract second col and remove extracted str from raw data
    UPDATE importTable
    SET rip_out_str2 = SUBSTRING(import_str,0,CHARINDEX(‘_’, import_str)),
    import_str = replace(import_str, rip_out_str2+’_’, ”)

    … etc as needed

    2005DBA

    Like

  21. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  22. Pingback: SQL SERVER - 2008 - Enhenced TRIM() Function - Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds Journey to SQL Authority with Pinal Dave

  23. I’m writing a batch job using .NET and want to compare a table in our data warehouse with a transactional table, These 2 tables reside on different servers and databases. I am creating a datahandler from .NET and have been told that creating 2 different data connections will not work ???

    Any other ideas or will 2 different connections work with two seperate data adaptors? Any ideas welcome or if there is a better idea please share.

    thanks

    Like

  24. —-URGENT—–

    i have bulk inserted data in my sql 2005 server then i use trim (ltrim & rtrim) function still SPACE IS NOT REMOVED.

    my imported table is all fileds are nvcahractor (255)

    could you please advice what is happening here ???….

    Like

  25. How do I do away with the extra characters in the field, sy that the third field will be displayed without the spaces.


    if exists (select top 1 1 from information_schema.routines where
    specific_name = ‘proc_DisplayHoursWorked’ and routine_type = ‘PROCEDURE’)
    DROP PROCEDURE proc_DisplayHoursWorked
    go
    CREATE PROCEDURE [dbo].[proc_DisplayHoursWorked]

    AS
    SELECT Employees.Name,
    Employees.Surname,
    Employees.Name + ‘ ‘ + Employees.Surname As [EmpName]
    FROM Timesheets INNER JOIN Employees
    ON Timesheets.UserNumber = Employees.UserNumber

    Like

  26. FOUND TO SOLUTION:


    if exists (select top 1 1 from information_schema.routines where
    specific_name = ‘proc_DisplayHoursWorked’ and routine_type = ‘PROCEDURE’)
    DROP PROCEDURE proc_DisplayHoursWorked
    go
    CREATE PROCEDURE [dbo].[proc_DisplayHoursWorked]

    AS
    SELECT rtrim(Employees.Name) + ‘ ‘ +
    ltrim(Employees.Surname) As [Emp Name]
    FROM Timesheets INNER JOIN Employees
    ON Timesheets.UserNumber = Employees.UserNumber

    Like

  27. Pingback: SQL SERVER – Connect Item – Vote for Feature Request Function TRIM Journey to SQL Authority with Pinal Dave

  28. Hi Raghuram,
    you can use this STUFF function to remove those characters

    select stuff(stuff(‘(’13,14,15′)’,2,1,”),10,1,”)

    Like

  29. Hi,

    You can use:

    UPDATE Table
    SET Column = LTRIM(RTRIM(Column))

    It will update all your rows and remove space before and after.

    Let me know if it helps you.

    Thanks,
    Tejas

    Like

  30. HI Pinal,
    Is there has any performance gain apart from using “ltrim (rtrim(col))” to UDF. Normally UDF bit less performance comparing system function.

    Thank you,
    Tharindu

    Like

  31. Pinal,

    I am trying to find a way to seperate a column into multiple columns, using a space as a delimeter. This would be similar to Excel’s ‘Text to Columns’ function. I have a field that has information like ‘FirstName LastName – Some Other Info’. What I need is just the first two bits of information as two seperate columns, so I can do some lookup and string functions against that data.

    Like

  32. CREATE FUNCTION employees_in_project
    (@pr_number varchar(20))
    RETURNS TABLE
    AS
    RETURN (SELECT Sum(Amount) as Amount
    FROM MasterDetail
    WHERE MasterDetail.code = @pr_number)
    GO

    In Analizer

    SELECT *, employees_in_project(master.code ) as Amount FROM Master
    GO

    it not work what is error in query

    Like

  33. Hi pinaldave ,

    Just wanted to say that you’ve got a great blog! In my various searches for clear instruction on how to do the various things that I find myself needing to do in SQL Server, I’ve found that it’s very close to a sure bet that if I find it on your blog, it’s going to be clear, and it’s going to work. Thanks!!!

    Like

  34. Pingback: SQLAuthority News – 1200th Post – An Important Milestone Journey to SQL Authority with Pinal Dave

  35. Trim leading/trailing whitespace and carriage returns, whilst retaining chars in the entity body.

    DECLARE @value VARCHAR(1000)
    DECLARE @pat varchar(10)
    SET @pat = ‘%[^ ‘ + char(09) + char(10) + ‘]%’

    SET @value = CHAR(10) + ‘ ‘ + CHAR(10) + ‘message test ‘ + char(10) + ‘ values ‘ + CHAR(10) + ‘ ‘ + CHAR(10) + CHAR(10) + ‘ ‘ + CHAR(10)

    SELECT SUBSTRING(
    @value,
    PATINDEX(@pat, @value),
    LEN(@value) – PATINDEX(@pat, @value) – PATINDEX(@pat, REVERSE(@value)) + 2)

    Like

    • Hi caractacus,

      Thank you for posting the more efficient method of trimming leading and trailing white space. I have a couple enhancements I would make, which i had to make on my side during testing.

      1) Use DATALENGTH() instead of LEN(), because by design, LEN() omits trailing spaces, but when used in the SUBSTRING() function with the subtraction of the 2 PATINDEX() values, it ends up removing too many characters, in the case where the original string had trailing spaces.

      2) I found that if the original string has ALL white-space characters (i.e. no non-whitespace characters), it returns the original string untouched, because the length minus 0 minus 0 plus 2 = length + 2. If you check for PATINDEX(@pat, @value) being 0, and simply returning ” (empty string), that should handle that case.

      Other than that, this is a very slick way of identifying the first and last non-whitespace characters in a string and using those to remove the whitespace characters leading up to them from both ends.

      Like

  36. This is a neat function and very often used when dealing with strings.
    SQL Server could in fact include this feature in the database core. Of course one could always create this UDF but it would be more user friendly if it was already available as part of the system functions.

    Like

  37. TRIM function is not implemented in SQL Server because doing a ltrim(rtrim()) can have serious performance penalties when dealing with large resultsets. If it is there by default, developers would tend to abuse its use.

    By the way, great stuffs you have in here Pinal. Thanks!

    Like

    • LOLLLL it’s not there because it might be mis-used… good one.

      UNIONS and JOINS can be expensive… better remove them as well….

      guess it’s more efficent to force a UDF reference to do the identical thing….

      Like

  38. Is there a way to select all columns (Select * from table) applying ltrim function on it. Meaning I don’t want to specify each column name in my query but I need the data returned without spaces.

    Thanks.

    Like

  39. @Ali.

    You want to do this in another function or a stored procedure or simple a select statement ? Of-course this could be in stored procedure. Please give us more information.

    ~ IM.

    Like

  40. Hi,

    Please suggest a way to remove more than 2 spaces from the middle of string. As already suggested above, I’ve tried using replace () function but not getting the desired output.

    ie.

    DROP FUNCTION dbo.Trim
    GO
    CREATE FUNCTION dbo.Trim (@string varchar(8000))
    RETURNS varchar (8000)
    BEGIN
    RETURN LTRIM (RTRIM (REPLACE (@string, ‘ ‘, ‘ ‘)))
    END
    GO

    Am calling this function as –

    SELECT dbo.Trim (‘ Hello Everybody ‘)

    And the output is – ‘Hello Everybody’ (Still contains 2 spaces in middle).

    How can I get just a single space in the middle ?

    Like

  41. Hello Mani,

    I think you are replacing 2 spaces with 1. For that the folowing code working perfrctly for me:

    CREATE FUNCTION dbo.Trim (@string varchar(8000))
    RETURNS varchar (8000)
    BEGIN
    RETURN LTRIM (RTRIM (REPLACE (@string, ‘ ‘, ‘ ‘)))
    END
    GO

    Please let me know if this doesn’t asnwer your question.

    Regards,
    Pinal Dave

    Like

  42. Hi Pinal,

    Thanks for responding to my query. But my query is to remove all the spaces from the string except the single one. Just like the TRIM () function in MS Excel and the other programming languages. Pls. suggest how can I implement it ?

    Regards.

    Like

    • Hi dude..
      For that use replace command….
      that means replace space to no space…

      eg:select replace(‘ Hari priya ‘,’ ‘,”)

      Like

  43. Hi Mr.Dave,

    I am working on a query where i have to seperate the records from a column and the column is like this-

    Category
    Request/Phone/Apply
    Break/Hp/Desktop

    Can you tell me how can i seperate the records like

    Category1 Category2 Category3
    Request Phone Apply
    Break Hp Desktop

    Thanks and Regards

    Nick

    Like

  44. Hi Nick,

    You can use XML to partition this string into columns as:

    DECLARE @xmlIDs XML
    DECLARE @Tmp TABLE(
    ID INT IDENTITY,
    Val VARCHAR(MAX)
    )

    INSERT INTO @Tmp(Val)
    SELECT ‘Request/Phone/Apply’
    UNION ALL
    SELECT ‘Break/Hp/Desktop’

    SELECT ID,
    x.query(‘IDs/ID[1]’).value(‘.’,’VARCHAR(MAX)’) AS C1,
    x.query(‘IDs/ID[2]’).value(‘.’,’VARCHAR(MAX)’) AS C2,
    x.query(‘IDs/ID[3]’).value(‘.’,’VARCHAR(MAX)’) AS C3
    FROM (
    SELECT *,
    CAST(‘
    ‘ + REPLACE(Val, ‘/’, ”) + ” +
    ” AS XML) AS X
    FROM @Tmp
    ) x

    For more reference you can go: http://www.sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html

    Thanks,

    Tejas

    Like

    • Hey Tejas,

      Thanks for your reply. let me show you what i have done.

      I wanna develop report using the query below but when i run it in SSRS it says “Invalid object name #temp”
      can you help me with this.

      Thanks

      Nick
      ——————————————–

      declare @string varchar(500)

      declare @i int
      declare @i1 int
      set @i1 =1
      declare @incident int

      declare @pos int
      declare @piece varchar(500)
      create table #temp(valmain int,val0 int, val1 varchar(50))

      DECLARE db_cursor CURSOR FOR
      select top 10 workitem_category_tree_value
      ,count(workitem_number) as Incidents
      from hd_workitem_current_view
      where workitem_created_on between ‘1/8/2010’and’1/15/2010′
      group by workitem_category_tree_value
      order by count(workitem_number)DESC

      OPEN db_cursor
      FETCH NEXT FROM db_cursor INTO @string,@incident

      WHILE @@FETCH_STATUS = 0
      BEGIN
      if right(rtrim(@string),1) ‘\’
      set @string = @string + ‘\’

      set @pos = patindex(‘%\%’ , @string)

      set @i =0
      insert into #temp values(@i1,@i,@incident )

      while @pos 0
      begin
      set @i=@i+1
      set @piece = left(@string, @pos – 1)
      print cast(@piece as varchar(500))
      print cast(@pos as varchar(500))
      insert into #temp values(@i1,@i,@piece)
      — You have a piece of data, so insert it, print it, do whatever you want to with it.
      –print cast(@piece as varchar(500))

      set @string = stuff(@string, 1, @pos, ”)
      set @pos = patindex(‘%\%’ , @string)
      end
      set @i1=@i1+1

      FETCH NEXT FROM db_cursor INTO @string,@incident

      END

      CLOSE db_cursor
      DEALLOCATE db_cursor

      SELECT valmain,
      [0], [1], [2], [3]
      FROM
      (SELECT valmain,val0, val1
      FROM #temp) AS SourceTable
      PIVOT
      (
      min(val1)
      FOR val0 IN ( [0], [1], [2], [3])
      ) AS PivotTable;

      drop table #temp
      ——————————————————————

      Like

      • Hi dude,

        Pls avoid using # table. while ur server get restarted or any network issue u will lose ur table.. use table and finally truncate it… this wont occupy much space too… ur coding correct anyhow..

        Like

  45. Hi,
    Could u pls help me by providing solution to the below problem.

    I have similar to below IIS requests, now I want to display only resource names from below requests,

    http://192.168.1.1/web/Styles/Globalsign.js

    for example, results should be like this…
    Globalsign.js
    simbol.jpg

    Kindly help me. I am using SQL 2008.

    Thanks,
    Kishore

    Like

    • Hi dude ..

      Check out

      declare @hp varchar(200)
      declare @hp1 varchar(200)

      set @hp = ‘http://192.168.1.1/web/Styles/Globalsign.js’
      set @hp1 = ‘http://192.168.1.1/web/forms/simbol.jpg’

      select reverse(substring(reverse(@hp),1,charindex(char(47),reverse(@hp),1)-1))
      select reverse(substring(reverse(@hp1),1,charindex(char(47),reverse(@hp1),1)-1))

      is it correct pinal pls tell…

      Like

  46. Hello Friends,

    I have a small doubt, Plz help. “This World is Beautiful”

    This is my String stored in DB but in output i only want

    “This World”

    Note: No double quote required.

    Plz suggest

    Regards
    Anupam

    Like

  47. Hi,

    I have to bind a nvarchar column to sql reporting services, and need to format it, if it has space between the 2 lines. Kindly help me how to do it?

    Like

  48. how can i reduce middle space in value ?
    ex:-
    lap top
    insert into table_1 (name) values (lap top);

    i want value “laptop” stored in database
    pls
    send method name

    Like

  49. While the trim() is convenient, it, as with all UDFs in SQL Server, can be a performance hog. Run the following and you can see a dramatic difference. I ran on SQL Server 2008:

    create function dbo.Trim
    (
    @string varchar(max)
    )
    returns varchar(max) as
    begin

    return( rtrim(ltrim(@string)) );

    end;
    go

    declare
    @i integer = 0,
    @dummy varchar(10),
    @start datetime;

    set @start = current_timestamp;
    while (@i < 10000) begin

    set @dummy = dbo.trim( ' foo bar ');
    set @i = @i + 1;

    end;

    print 'UDF: ' + cast(datediff( millisecond, @start, current_timestamp) as varchar);

    set @i = 0;

    set @start = current_timestamp;
    while (@i < 10000) begin

    set @dummy = rtrim(ltrim(' foo bar '));
    set @i = @i + 1;

    end;
    print 'Built-ins: ' + cast(datediff( millisecond, @start, current_timestamp) as varchar);

    My results consistently show the UDF taking about 30 times longer to perform.

    Like

  50. Hello,

    Can anyone tell me how to call any UDF without schema name?

    I have created Trim function but while calling it my requirement is i should not use dbo.Trim instead i will be using Trim only. is there any way to do that?

    I have tried creating sys.Trim also but it is throwing me error as

    The specified schema name “sys” either does not exist or you do not have permission to use it.

    Can anyone tell me how to do this?

    Like

  51. I am using SQL SERVER 2005..
    I am able to trim values using LTRIM with the following
    SELECT LTRIM(‘ ZAM000000017′) it is giving proper value as ZAM000000017
    But when I am trying to use Update Statement to update the value
    Update INTERMEDIATE_ISIN
    Set ISIN_CODE = LTRIM(ISIN_CODE);

    is not working…. !!!

    Please help

    Like

  52. hi this is nani,

    Can anyone tell me how to get email id value with out alias name like.. i hav id “naresh@softsolvers.co.my” i want only “naresh” for result..
    please help..
    thanq,

    Like

  53. Hi Pinal,

    I am having an issue while trimming the below word.

    ETF factsheets request

    Select RTrim(Len(Comments)), Comments from De_Activity Where [ID] = 1470
    Select LTrim(Len(Comments)), LTrim(Comments) from De_Activity Where [ID] = 1470

    Total length of string is 28 (Sql). But when I do the trim in excel I get len as 22.

    I have used charindex to find ‘E’, output was 4.
    So there are 3 spaces before E, but are not getting trimmed.

    Regards,
    Uday

    Like

  54. Hi.
    I am using MS SQL 2008. I have a table X in DB which has some y columns. One of the column values has spaces suffixed and LTRIM, RTRIM is not working. Please let me kow if there is any other way to get rid of spaces.

    Like

  55. Hi..I’m facing a problem from containstable in sql server.I wrote a search procedure including containstable.the results came fine.but i’m facing problem with the search strings “all” and “h&a”.i didn’t got any result when using these strings.but in table keywords(All and H&A) is there can u please help to me for avoid the problem.

    Like

  56. How can i remove middle characters from a string?
    eg: phaninder,reddy is a name……..i would like to remove ‘,’ from this name using trim function..pls give me the query…..

    Like

  57. If I use
    SELECT *
    FROM Employee
    Where LTRIM(RTRIM((empcode)) = ‘E123′

    It return the result but if I omit the LTRIM and RTRIM , it shows no record found.
    How to get the result without using LTRIM and RTRIM

    Like

  58. Hi,
    I am using SQL 2008, and am trying to insert value from table 1 to table 2 with multi records from table1 column “Description” and would like to populate in 1 unique record into table 2.

    I have given an example below;

    TABLE 1

    PATIENT_ID DESCCRIPTION

    2771 MOTRIN TAB
    2771 TYLENOLTAB
    2771 ZANTAC TAB

    2775 ABC TAB
    2775 123 TAB
    2775 XYZ TAB

    Example: Copy values from table 1 into table 2 with single row;

    TABLE 2

    PATIENT_ID DESCRIPTION

    2771 MOTRIN TAB,TYLENOL TAB,ZANTAC TAB
    2775 ABC TAB, 123 TAB, XYZ TAB

    Thanks

    Like

  59. I am suffering from a problem. there is one database field that is entered by users. User may some time entered lot of spaces between characters. This problems in crystal report. few line print on one page and other lines print on 2nd and 3rd page. I checked but this issue not belong to crystal report formatting. I search a lot for crystal report formulas to get solution but i can’t.
    I want to remove empty lines between this string. how can i do it?

    Thanks

    Like

  60. HI,

    Is there something in SQL Server to make Ltrim as
    default Constraint or policy in Database
    For All Tables

    eg.

    SELECT ‘ a ‘ A INTO TestTable

    And on select Statement, This Table return only ‘a’ without Leading\Trailing Spaces.

    Like

  61. Thank you for your post!

    I was looking for a function to turn any number of consecutive spaces into a single space, for example ‘I love Pinal Dave’ -> ‘I love Pinal Dave’. I came up with…

    CREATE FUNCTION dbo.SPACEOUT(@string VARCHAR(255))
    RETURNS VARCHAR(255)
    BEGIN
    DECLARE @doublespaceindex TINYINT,
    @tempstring VARCHAR(255)
    SET @doublespaceindex = CHARINDEX(‘ ‘,@string)
    WHILE @doublespaceindex > 0
    BEGIN
    SET @string = REPLACE(@string,’ ‘,’ ‘)
    SET @doublespaceindex = CHARINDEX(‘ ‘,@string)
    END
    RETURN @string
    END

    Like

  62. Pingback: SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video « SQL Server Journey with SQL Authority

  63. hi
    how can i select data that contains in [ ] in sql column.
    e.g.i have a column that contains large data in that col i have some data as –
    employee name is [emp_name] and job description is [job_desc].
    and i want to select only [emp_name] using sql qry n display it in stringbuilder.
    thanks

    Like

  64. Hello, my doubt is can we use ltrim/rtrim on integers and date variables.
    this works pretty well, but certain coding standards avoid the use of trimming integer or date variables. Can you please suggest why so?

    Like

  65. hi,
    i have a problem, want to retrieve & from table in asp.net page .
    but the result is & is display in textbox please sort out this problem

    Like

  66. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s