7

I have data I cleaning up in an old data table prior to moving it to a new one. One of the fields has spaces in the column, right & left. I wrote the following code to address this and still have leading spaces?? The bulk of the data is clean when using this code but for some reason there are spaces prior to RT addresses... Has anyone else had this type of issue? 

,CASE   
WHEN PropStreetAddr IS NOT NULL
 THEN  (CONVERT(VARCHAR(28),PropStreetAddr))  
WHEN PropStreetAddr is NOT NULL Then  (Select LTrim(RTrim(PropStreetAddr)) As PropStreetAddr)
     ELSE NULL END  as 'PROPERTY_STREET_ADDRESS'

Sample output data:

1234 20th St 
  RT 1 BOX 2  
560 King St  
610 Nowland Rd  
  RT 1  
1085 YouAreHere Ln  
  RT 24 Box 12
skaffman
  • 398,947
  • 96
  • 818
  • 769
JMS49
  • 263
  • 1
  • 8
  • 18
  • 2
    Duplicate of [Remove trailing empty space in a field content](http://stackoverflow.com/questions/1864838/remove-trailing-empty-space-in-a-field-content) - see my answer for how to remove non-printable ASCII – OMG Ponies Jul 30 '10 at 19:58

4 Answers4

11

I've had the same problem - wrapping the string in a CAST(x as varbinary(64)) shows the hex and from this I see A000 - which I believe is non-breaking space.

To remove, try this (for UNICODE);

LTRIM(RTRIM(REPLACE(my-column, NCHAR(0x00A0), '')))
Mark McGee
  • 111
  • 1
  • 3
  • 1
    +1. I just had this problem and this helped me. I would have never guessed this Unicode char was bothering me. – Hanlet Escaño Mar 06 '13 at 23:19
  • 1
    +1 `CAST(x AS VARBINARY(64))` is the best advice. Quick comparison of differences in the hexadecimal makes all the difference. – user692942 Mar 10 '14 at 13:06
7

Use:

WHEN PropStreetAddr is NOT NULL THEN
   (SELECT LTRIM(RTRIM((REPLACE(PropStreetAddr, 
                                SUBSTRING(PropStreetAddr, 
                                          PATINDEX('%[^a-zA-Z0-9 '''''']%', PropStreetAddr), 1), '') AS PropStreetAddr)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Still no change, I am stumped. – JMS49 Jul 30 '10 at 20:44
  • @JMS49: Did you try just the REPLACE & it's contents? Only other thing I can think of is to get the data into an editor where you can turn on the visibility of non-visible content (backwards P in Textpad, for instance). – OMG Ponies Jul 30 '10 at 20:51
  • Yes I used the code as you wrote it. Put the data in WORD and asked it to show all hidden characters.. sample data: ..124.. WOODLEY PARK RD ..1085...TRAIL CT ..765...K ST ..506..AVE B .1500..R..ST ..760...N STREET .1865..D ST .1810...A..STREET ..645...15TH..ST .....RT..1. This leads me to think that the trim combo is not working, but why? – JMS49 Jul 30 '10 at 21:14
  • @JMS49: LTRIM/RTRIM only works on spaces at the respective edge - this will change two spaces into one: `REPLACE(PropStreetAddr, ' ', ' ')` Stupid comment eats spaces - should be two in the first, one in the second. – OMG Ponies Jul 30 '10 at 21:19
  • UPDATE #LTS_MAP SET MERS_ID = REPLACE(MERS_ID, SUBSTRING(MERS_ID, PATINDEX('%[^a-zA-Z0-9 '''''']%', MERS_ID), 1), '') WHERE PATINDEX('%[^a-zA-Z0-9 '''''']%', MERS_ID) <> 0 NULL NULL NULL NULL NULL NULL NULL the blank is Paragrah entries. This appears in several columns. I have run both the substring replace code & the convert (ltrim(rtrim and still did not reomve the little rascal . – JMS49 Jul 30 '10 at 22:25
  • @JMS49: Post a new question, link back to this one. – OMG Ponies Aug 02 '10 at 22:54
6

Here's the expression that will work. I'm assuming there is no non-visible content. You should still pursue @OMG Ponies recommendation if you suspect it. And I think the PATINDEX expression can be added to this expression if you must deal with the non-visible content.

SQL Server CASE processes only one WHEN clause then breaks. So you are never getting to the second data conversion. Also, all NULL values will convert to NULL when you use the LTRIM and RTRIM functions. So, you don't need to test for it, unless you want to do something with the NULLs.

So, try this:

CONVERT(VARCHAR(28), LTRIM(RTRIM(PropStreetAddr))) as [PROPERTY_STREET_ADDRESS]
bobs
  • 21,844
  • 12
  • 67
  • 78
  • Bobs and @OMG Ponies you are the greatest it works! Terrific, I think my headache will now go away too! Thank you both. There are some wonderful folks on this site. – JMS49 Jul 30 '10 at 21:32
  • This is obviously quite an old answer now but for those coming here from SO or Google I wouldn't recommend this method especially if you deal with internationalization. Take a look at @MarkMcGee [answer](http://stackoverflow.com/a/12600716/692942) for the correct way to dissect this issue. – user692942 Mar 10 '14 at 13:08
1

I have a similar situation, initially I thought the LTRIM & RTRIM functions were not working correctly. However once I tested it out and could see that it was not a proper white space character (the actual character may be different to your offending non printable character), using:

ASCII

I found the character to be named 160, so then I did a replace like:

SELECT REPLACE('NaughtyString', CHAR(160),'')

Hope it helps somebody

mickeyziggyk
  • 11
  • 1