remove spaces in strings

Question

Post reply

remove spaces in strings

komal145

SSCrazy Eights

Points: 9918
More actions
September 5, 2017 at 12:29 pm

#339686

hi ,
i hvave a value something like this column scrapetype "WebCrawler.Crawlable.EURRUSSIA.C_Darty ". My join is on this column and it does not appear because guessing of spaces. how can we remove it? I tried replace,ltrim and rtrim it doe snot work.
CREATE TABLE #temp
( iD INT identity(1,1)
,CODE Varchar(20)
,Crawler Nvarchar(200)
,Pricetype varchar(20)
)
INSERT into #temp values('DARTY','WebCrawler.Crawlable.EURRUSSIA.C_Darty ','ASP')

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

You could use REPLACE(), but there are no spaces in that string to begin with.

DECLARE @TextWithSpaces VARCHAR(100) = 'I have a whole lot of spaces';
PRINT REPLACE(@TextWithSpaces,' ','');

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

"WebCrawler.Crawlable.EURRUSSIA.C_Darty "
"WebCrawler.Crawlable.EURRUSSIA.C_Darty"

This is how it shows in the table and length differes as 32 and 31. i tried replace , ltrim , rtrim .It is not working.

drew.allen SSC Guru Points: 76996 More actions · Answer 3

komal145 - Tuesday, September 5, 2017 12:59 PM
"WebCrawler.Crawlable.EURRUSSIA.C_Darty "
"WebCrawler.Crawlable.EURRUSSIA.C_Darty"
This is how it shows in the table and length differes as 32 and 31. i tried replace , ltrim , rtrim .It is not working.

My best guess is that it's another white space character such as a tab, carriage return, or line feed. You can also find out which by selecting ASCII(RIGHT(Crawler)).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

komal145 - Tuesday, September 5, 2017 12:29 PM
hi ,
i hvave a value something like this column scrapetype "WebCrawler.Crawlable.EURRUSSIA.C_Darty ". My join is on this column and it does not appear because guessing of spaces. how can we remove it? I tried replace,ltrim and rtrim it doe snot work.
CREATE TABLE #temp
( iD INT identity(1,1)
,CODE Varchar(20)
,Crawler Nvarchar(200)
,Pricetype varchar(20)
)
INSERT into #temp values('DARTY','WebCrawler.Crawlable.EURRUSSIA.C_Darty ','ASP')

Post your real code because trailing spaces aren't normally considered during character based joins. There's something else going on here.

Also, if REPLACE isn't killing the spaces, then that's not a space in the data. Chances are pretty good that it's CHAR(140), which is a "non-breakable hard space". Use ASCII(RIGHT(datacolumhere,1) to find out what it is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 5

Jeff Moden - Tuesday, September 5, 2017 1:26 PM
Post your real code because trailing spaces aren't normally considered during character based joins. There's something else going on here.
Also, if REPLACE isn't killing the spaces, then that's not a space in the data. Chances are pretty good that it's CHAR(140), which is a "non-breakable hard space". Use ASCII(RIGHT(datacolumhere,1) to find out what it is.

Char(160) is the non-breakable space.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Try this code (adapt it to your table) to find out what the problematic character is.
WITH E(n) AS( SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n) ), E2(n) AS( SELECT a.n FROM E a, E b ), E4(n) AS( SELECT a.n FROM E2 a, E2 b ), cteTally(n) AS( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM E4 ) SELECT e.Crawler, SUBSTRING(e.Crawler, a.n, 1), ASCII(SUBSTRING(e.Crawler, a.n, 1)) FROM cteTally a JOIN #temp e ON a.n <= LEN(e.Crawler)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

ThomasRushton - Wednesday, September 6, 2017 7:35 AM
Jeff Moden - Tuesday, September 5, 2017 1:26 PM
Post your real code because trailing spaces aren't normally considered during character based joins. There's something else going on here.
Also, if REPLACE isn't killing the spaces, then that's not a space in the data. Chances are pretty good that it's CHAR(140), which is a "non-breakable hard space". Use ASCII(RIGHT(datacolumhere,1) to find out what it is.
Char(160) is the non-breakable space.

You're absolutely correct. Thank you for the correction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anand08sharma SSC Enthusiast Points: 180 More actions · Answer 8

komal145 - Tuesday, September 5, 2017 12:29 PM
hi ,
i hvave a value something like this column scrapetype "WebCrawler.Crawlable.EURRUSSIA.C_Darty ". My join is on this column and it does not appear because guessing of spaces. how can we remove it? I tried replace,ltrim and rtrim it doe snot work.
CREATE TABLE #temp
( iD INT identity(1,1)
,CODE Varchar(20)
,Crawler Nvarchar(200)
,Pricetype varchar(20)
)
INSERT into #temp values('DARTY','WebCrawler.Crawlable.EURRUSSIA.C_Darty ','ASP')

select LTRIM(RTRIM(column)) from table;
You can use above Ltrim and Rtrim to remove preceding and leading spaces

drew.allen SSC Guru Points: 76996 More actions · Answer 9

anand08sharma - Wednesday, September 13, 2017 11:07 AM

komal145 - Tuesday, September 5, 2017 12:29 PM
hi ,
i hvave a value something like this column scrapetype "WebCrawler.Crawlable.EURRUSSIA.C_Darty ". My join is on this column and it does not appear because guessing of spaces. how can we remove it? I tried replace,ltrim and rtrim it doe snot work.
CREATE TABLE #temp
( iD INT identity(1,1)
,CODE Varchar(20)
,Crawler Nvarchar(200)
,Pricetype varchar(20)
)
INSERT into #temp values('DARTY','WebCrawler.Crawlable.EURRUSSIA.C_Darty ','ASP')

select LTRIM(RTRIM(column)) from table;
You can use above Ltrim and Rtrim to remove preceding and leading spaces

If you had actually bothered to read the post that you quoted, you would see that he already tried that and it didn't work.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

As drew said, you are likely dealing with a non-space whitespace character; what Luis posted will help you identify what that character is.

This can easily be resolved using patexclude8K like so:

-- some text with a tab at the end (which will look like a space in SSMS) declare @txt varchar(100) = '"WebCrawler.Crawlable.EURRUSSIA.C_Darty'+char(9)+'"'; print @txt; -- note the output

--solution: return any text that is not alphabetical a space, quote, dot or underscore. select newString from dbo.patExclude8K(@txt, '[^a-zA-Z"._]');

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001