RE: LTRIM and RTRIM not working

SSC Guru

Points: 186127

May 29, 2014 at 9:38 am

GBeezy (5/29/2014)
I am not getting an error...
Before the function call I using the ASCII(RIGHT(t.OffensiveColumn,1)) to see what the ASCII character is, then calling the function, and returning the results to a temp table. Then on my temp table, the column that the function was on, I am running the ASCII(RIGHT(r.RemovedASCII) and the values values are the same.
Starting ASCII value 68 - ASCII values after function call is 68.

Without seeing what you're looking at on your screen, this is really hard to interpret. You could make it a lot easier for us by showing the code you are using for this process, in addition to attempting to describe it. Anyway, try this, substituting one of your own actual "dirty" data values for the artificial one used for testing in the script:

DECLARE @MyVar NVARCHAR(100)

SET @MyVar =

'A'+CHAR(1)+

'B'+CHAR(1)+'C'+CHAR(2)+'D'+CHAR(3)+'E'+CHAR(4)+'F'+CHAR(5)+'G'+CHAR(6)+'H'+CHAR(7)+'I'+CHAR(8)+

'J'+CHAR(9)+'K'+CHAR(10)+'L'+CHAR(11)+'M'+CHAR(12)+'N'+CHAR(13)+'O'+CHAR(14)+'P'+CHAR(15)+'Q'+CHAR(16)+

'R'+CHAR(17)+'S'+CHAR(18)+'T'+CHAR(19)+'U'+CHAR(20)+'V'+CHAR(21)+'W'+CHAR(22)+'X'+CHAR(23)+'Y'+CHAR(24)+

'Z'+CHAR(25)+'1'+CHAR(26)+'2'+CHAR(27)+'3'+CHAR(28)+'4'+CHAR(29)+'5'+CHAR(30)+'6'+CHAR(31)+'7'+CHAR(32)

SELECT

OffensiveColumn,

CleanedValue =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(OffensiveColumn COLLATE LATIN1_GENERAL_BIN,CHAR(0),'')

,CHAR(1),''),CHAR(2),''),CHAR(3),''),CHAR(4),''),CHAR(5),''),CHAR(6),''),CHAR(7),''),CHAR(8),'')

,CHAR(9),''),CHAR(10),''),CHAR(11),''),CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(15),''),CHAR(16),'')

,CHAR(17),''),CHAR(18),''),CHAR(19),''),CHAR(20),''),CHAR(21),''),CHAR(22),''),CHAR(23),''),CHAR(24),'')

,CHAR(25),''),CHAR(26),''),CHAR(27),''),CHAR(28),''),CHAR(29),''),CHAR(30),''),CHAR(31),''),CHAR(32),'')

FROM (SELECT OffensiveColumn = @MyVar) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden