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:
[youtube=http://www.youtube.com/watch?v=1-hhApy6MHM]There will be no spaces around them. It is very little but useful trick.
Reference : Pinal Dave (https://blog.sqlauthority.com)
127 Comments. Leave new
In sql server TRIM function not found to trim data , but it provides LTRIM for left trim & RTRIM for right trim .
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
Abdul –
SELECT *
FROM Employee
Where empcode LIKE ‘%E123%′
It means that the empcode has spaces as part of it. You need to use trim functions
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
You need to use PIVOT. Refer this
i need replace Single Quotes :
my input is : who’s,this,book’s
i want the output is : whos
this
books
SELECT REPLACE (REPLACE (‘who’s,this,book’s’,’’’,”),’,’,’ ‘)
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
Hi, Can we use this one to the entire column of the table?
something like ”Select Trim([Column Name]);”
Thanks
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.
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
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
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?
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
Hey wat about the spaces which are between some characters?
Magnifica aportacion … GRACIAS !!!
How to remove spaces in themiddle of the function while inserting the data into mysql
My Function:
CREATE FUNCTION StrTrim(@Str VARCHAR(MAX)) RETURNS VARCHAR(MAX) BEGIN
DECLARE @NewStr VARCHAR(MAX) = NULL
IF (@Str IS NOT NULL) BEGIN
SET @NewStr = ”
DECLARE @WhiteChars VARCHAR(4) =
CHAR(13) + CHAR(10) — ENTER
+ CHAR(9) — TAB
+ ‘ ‘ — SPACE
IF (@Str LIKE (‘%[‘ + @WhiteChars + ‘]%’)) BEGIN
;WITH Split(Chr, Pos) AS (
SELECT
SUBSTRING(@Str, 1, 1) AS Chr
, 1 AS Pos
UNION ALL
SELECT
SUBSTRING(@Str, Pos, 1) AS Chr
, Pos + 1 AS Pos
FROM Split
WHERE Pos = (
SELECT MIN(Pos)
FROM Split
WHERE CHARINDEX(Chr, @WhiteChars) = 0
)
AND Pos <= (
SELECT MAX(Pos)
FROM Split
WHERE CHARINDEX(Chr, @WhiteChars) = 0
)
END
END
RETURN @NewStr
END
Source:
Thanks for your comment Eduardo.
Other script:
DECLARE @Str NVARCHAR(MAX) = N’
foo bar
Foo Bar
‘
PRINT ‘[‘ + @Str + ‘]’
DECLARE @StrPrv NVARCHAR(MAX) = N”
WHILE ((@StrPrv @Str) AND (@Str IS NOT NULL)) BEGIN
SET @StrPrv = @Str
— Beginning
IF EXISTS (SELECT 1 WHERE @Str LIKE ‘[‘ + CHAR(13) + CHAR(10) + CHAR(9) + ‘]%’)
SET @Str = LTRIM(RIGHT(@Str, LEN(@Str) – 1))
— Ending
IF EXISTS (SELECT 1 WHERE @Str LIKE ‘%[‘ + CHAR(13) + CHAR(10) + CHAR(9) + ‘]’)
SET @Str = RTRIM(LEFT(@Str, LEN(@Str) – 1))
END
PRINT ‘[‘ + @Str + ‘]’
Result:
[
foo bar
Foo Bar
]
[foo bar
Foo Bar]
Hi, my query is not related to SQL Server, but to Informix Database. Do you have any idea how efficient is RTRIM method in an SQL query run on an Informix Database, containing about 50,000 rows.
you should just use RTrim(LTrim(value)) and that is it.
Oh yeah – you can.
How to trim a field with data type “text”
Probably, triming after converting it to varchar or nvarchar value!