Truncating Spaces - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Truncating Spaces

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 13:52:19

Gurus
I have a column with data type as varchar
Like
phone
202-249- 0011

Now i want to eliminate these spaces before 0011 and it should appear as 202-249-0011
Please help
Regards
Nitin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 13:57:45

check out this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-22 : 13:58:41

UPDATE YourTable SET YourColumn = REPLACE(YourColumn, ' ' , '')

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 14:11:18

This query is not working.
Regards

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 14:13:07

I guess that referenced topic could be a little overboard for just eliminating a single space

But there's some great stuff there...

My wife would say, "I asked you the time and you built me a clock!"

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-22 : 14:13:45

It works fine for me. Here's some sample code to prove it:


CREATE TABLE YourTable(YourColumn varchar(20))

INSERT INTO YourTable VALUES('202-249- 0011')

SELECT * FROM YourTable

UPDATE YourTable SET YourColumn = REPLACE(YourColumn, ' ' , '')

SELECT * FROM YourTable

DROP TABLE YourTable

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 14:18:21

It contains 10,000 resords.it canontains different data.
some records are like this
202-296- 8696
202- 789-1397
i am running this
UPDATE Restdatascrap1 SET restphone = REPLACE(restphone, ' ' , '')

result is the same
Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-22 : 14:20:32

The code works fine for the data that you have provided. Run this in Query Analyzer to see:


CREATE TABLE YourTable(YourColumn varchar(20))

INSERT INTO YourTable VALUES('202-249- 0011')
INSERT INTO YourTable VALUES('202-296- 8696')
INSERT INTO YourTable VALUES('202- 789-1397')

SELECT * FROM YourTable

UPDATE YourTable SET YourColumn = REPLACE(YourColumn, ' ' , '')

SELECT * FROM YourTable

DROP TABLE YourTable

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 14:30:20

Thanks.Yeah your query is fine.but my results are still unchanged.What can be the reason.
Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-22 : 14:33:17

Well what are the results?

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 14:37:56

"What can be the reason"

Does it say "10,000 row(s) effected" ?

Could there be a transaction which is being rolled back? (Or hasn't, yet, been committed?)

Is it possible that the gap is not a space, but something else - tab? Extended character which happens to look like a space?

Or a space with a different collation? (Is that even possible Arnold?!)

Kristen

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 14:45:19

Yes it says 10,000 rows affected.
How do i find this out?
Regards

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 14:49:32

"How do i find this out?"

Type ROLLBACK and see if you get an error (in which case the transaction was committed)

"10,000 rows affected" only means that it updated 10,000 rows - not that it actually found anything to REPLACE !!

Try:

SELECT TOP 100 restphone, REPLACE(restphone, ' ' , '')
FROM Restdatascrap1
WHERE restphone LIKE '% %'

and see what the Before and After look like.

Are you connected to the right database?

Kristen

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 14:57:40

Hi
The results are the same.oh man this will kill me.Any guesses

Regards

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 15:09:26

How do you mean the results are the same - the column on the left is the same as the column on the right?

Kristen

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 15:16:41

Hi
I mean when i run this i do not see any result.Both the columns are blank
Regards
Nitin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 16:04:28

Did they give up on you nitin1353?

>>Both the columns are blank
Do you mean there are no rows returned from this statement?

SELECT TOP 100 restphone, REPLACE(restphone, ' ' , '')
FROM Restdatascrap1
WHERE restphone LIKE '% %'

That means that there are no more spaces in your restphone column, right?

Be One with the Optimizer
TG

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 17:07:56

Yes there are no rows returned,but the data is same as before.
Regards

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-22 : 17:27:08

well then that means there are no more spaces in the column, no?

Go with the flow & have fun! Else fight the flow

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-03-22 : 17:28:34

Spaces are still there

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 17:31:50

Hmmm...Kristen might be on to something. (it looks like a space but maybe something else)

come up with a statement that will return just 1 row with a spacey phonenumber. Something like:

select restphone from Restdatascrap1 WHERE <id> = ?

ie: 9th character is a space-like-thing:
202-249- 0011

change this substring function to point to whichever character is the space and use the same WHERE clause from above to run this statement:


select	ascii(substring(restphone, 9,1)) 
from    Restdatascrap1 where <id> = ?

the result will be an ascii code. Paste that into this thread so we can see what character your <space> is.

Be One with the Optimizer
TG

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources