Leading and Trailing Spaces

Question

I am having a issue with Leading and Trailing Spaces. I have values that are being showed duplicates because they have leading, Trailing or any unoticiable ascii characters. I want to ensure that i remove them so the duplicates values can be handled.  I tried using in Query but i still had no success.

thoguhts ?

SELECT  Ltrim(Rtrim(FTA.BeforeValue)) BeforeValue, LEN(Ltrim(Rtrim(FTA.BeforeValue)))
FROM            Fact_TicketAudit AS FTA INNER JOIN
                       
                         Dim_Auditfield AS AF ON FTA.AuditFieldID = AF.AuditFieldID
 Where (AF.AuditFieldID = (10002)) 
 
 Group by Ltrim(Rtrim(FTA.BeforeValue)),  LEN(Ltrim(Rtrim(FTA.BeforeValue)))
Order by Ltrim(Rtrim(FTA.BeforeValue))

Answer 1

this might help:

http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/

http://www.tainyan.com/codesnippets/entry-63/sql-function-to-remove-special-characters.html

can you please provide sample data.

Answer 2

try :

CREATE FUNCTION [dbo].[ufn_RemoveSpecialChars]
(
    @Input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)  
BEGIN
    
   DECLARE @Output VARCHAR(MAX  )
    
   IF (ISNULL(@Input,'')='')
      SET @Output = @Input
   ELSE
   BEGIN
    
    DECLARE @Len INT
    DECLARE @Counter INT
    DECLARE @CharCode INT
         
    SET @Output = ''
    SET @Len = LEN(@Input)
    
   SET @Counter = 1
    
       WHILE @Counter <= @Len 
       BEGIN
           
          SET @CharCode = ASCII(SUBSTRING(@Input, @Counter, 1))
          IF @CharCode=32 OR @CharCode BETWEEN 48 and 57 OR @CharCode BETWEEN 65 AND 90 OR @CharCode BETWEEN 97 AND 122  
             SET @Output = @Output + CHAR(@CharCode)
              
          SET @Counter = @Counter + 1
           
       END
          
   END
       
   RETURN Ltrim(rtrim(@Output))
    
END
---
SELECT dbo.[ufn_RemoveSpecialChars](' This^ is $- sample ~text.#&*' )

Answer 3

Nested REPLACE is a fast way of getting rid of unwanted characters:

http://www.sqlusa.com/bestpractices2008/nestedreplace/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 4

Oops, somehow I closed the window before posting.

I have two blogs on a similar topic

http://beyondrelational.com/modules/2/blogs/78/posts/11140/cleanse-all-character-fields-in-a-table.aspx

and

http://beyondrelational.com/modules/2/blogs/78/posts/11141/remove-bad-characters-from-a-string.aspx

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Are your columns CHAR, by any chance instead of VarChar?  (This would explain Ltrim/Rtrim not behaving as expected with spaces)

Answer 6

I tried SEVERAL times to click 'Propose As Answer' for Naomi's answer.  I get an error message every time.  I tried in 2 browsers; I logged out and logged in again; I just keep getting error messages here.  Oh well.  That would be my proposed answer.

Great job, now and always, Naomi!!

---

Ryan Shuell

Answer 7

I tried SEVERAL times to click 'Propose As Answer' for Naomi's answer.  I get an error message every time.  I tried in 2 browsers; I logged out and logged in again; I just keep getting error messages here.  Oh well.  That would be my proposed answer.

Great job, now and always, Naomi!!

---

Ryan Shuell

Thanks for reporting.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012