I'm trying to remove white spaces from a string in SQL but LTRIM
and RTRIM
functions don't seem to work?
Column:
[ProductAlternateKey] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL
Query:
select REPLACE(ProductAlternateKey, ' ', '@'),
LEN(ProductAlternateKey),
LTRIM(RTRIM(ProductAlternateKey)) AS LRTrim,
LEN(LTRIM(RTRIM(ProductAlternateKey))) AS LRLen,
ASCII(RIGHT(ProductAlternateKey, 1)) AS ASCIIR,
ASCII(LEFT(ProductAlternateKey, 1)) AS ASCIIL,
ProductAlternateKey
from DimProducts
where ProductAlternateKey like '46783815%'
Result:
| COLUMN_0 | COLUMN_1 | LRTrim | LRLen | ASCIIR | ASCIIL | PRODUCTALTERNATEKEY |
---------------------------------------------------------------------------------
| 46783815 | 8 | 46783815| 8| 53 | 52 | 46783815 |
| 46783815 | 10|46783815 | 10| 10 | 52 | 46783815 |
Can it be other symbols if LTRIM
and RTRIM
are not working, like "Enter"?
7条答案
按热度按时间xa9qqrwz1#
Using
ASCII(RIGHT(ProductAlternateKey, 1))
you can see that the right most character in row 2 is a Line Feed or Ascii Character 10.This can not be removed using the standard
LTrim
RTrim
functions.You could however use
(REPLACE(ProductAlternateKey, CHAR(10), '')
You may also want to account for carriage returns and tabs. These three (Line feeds, carriage returns and tabs) are the usual culprits and can be removed with the following :
If you encounter any more "white space" characters that can't be removed with the above then try one or all of the below:
This list of potential white space characters could be used to create a function such as :
Which you could then use as follows:
kd3sttzy2#
In that case, it isn't space that is in prefix/suffix.
The 1st row looks OK. Do the following for the contents of 2nd row.
and
idv4meu83#
There may be 2 spaces after the text, please confirm. You can use
LTRIM
andRTRIM
functions also right?Maybe the extra space isn't ordinary spaces (ASCII 32, soft space)? Maybe they are "hard space", ASCII 160?
0ve6wy6x4#
How about this?
huus2vyu5#
Looks like the invisible character -
Try this
Raj
Edit: Based on ASCII() results, try
ALT+10
- use numeric keypad6vl6ewon6#
This is a pretty old post, but for the sake of others, by default, TRIM only trims standard spaces, but you can readily override that. It's pretty readable (and if you don't want to remove the whitespace within the string, it's the function to use). Just add a 'FROM' clause within the TRIM, listing all of the characters that might be present as an expression before 'FROM', like...
would remove leading and trailing spaces, dollar signs, tabs, returns and linefeeds from the string. You can, of course, assign the expression to a variable and reference it as needed in your code to make it more readable and maintainable...
zvokhttg7#
Remove new line characters with SQL column data