Remove space from string - 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

General SQL Server Forums

New to SQL Server Programming

Remove space from string

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-05-30 : 04:44:21

DECLARE @Phrase VarChar(1000)
SET @Phrase = '123 45'
SELECT REPLACE(@Phrase, ' ', '')
The above code returns the original string. How can I get it as '12345'?

In addition, the above code is not working in MSSQL 7. It seems to be ok in other versions?
How to remove space in SQL 7?

shaggy
Posting Yak Master

248 Posts

Posted - 2011-05-30 : 05:04:13

TRY THIS

DECLARE @Phrase VarChar(1000)
SET @Phrase = '123 45'
SELECT quotename(REPLACE(@Phrase, ' ', ''),'''')

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-05-30 : 05:18:42

The above code with quotename gives back the string embedded in quote.The expected result was 12345.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-30 : 05:36:31

Have you tried this?

DECLARE @Phrase VarChar(1000)
SET @Phrase = '123 45'
SELECT REPLACE(@Phrase, ' ', '')

Also check if REPLACE function is supported in version 7

Madhivanan

Failing to plan is Planning to fail

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-30 : 05:37:38

so you're saying that REPLACE(@Phrase, ' ', '') does not work with sql server 7.0??

Where software development knowledge meets the reader

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-05-30 : 05:54:05

Madhivanan,
It seems you have posted my question as response.It seems it is replacing all other character other than space in sql 7.
Is there any workaround to replace the space in sql 7?

quote:
Originally posted by madhivanan

Have you tried this?

DECLARE @Phrase VarChar(1000)
SET @Phrase = '123 45'
SELECT REPLACE(@Phrase, ' ', '')

Also check if REPLACE function is supported in version 7

Madhivanan

Failing to plan is Planning to fail

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-30 : 06:15:55

what do you mean replacing all other character other than space? so if you try with a different character.. it works fine?

Where software development knowledge meets the reader

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-05-30 : 06:38:34

Yes, It gives me 123A45 if I give to replace with character A in place of space.
DECLARE @Phrase VarChar(1000)
SET @Phrase = '123 45'
SELECT REPLACE(@Phrase, ' ', 'A')

What I required is to remove the in between space from the string.

quote:
Originally posted by jfarrugia

what do you mean replacing all other character other than space? so if you try with a different character.. it works fine?

Where software development knowledge meets the reader

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-30 : 07:54:37

hm interesting.. what if you replace current space with a specific word .. lets say [%%] and following replace [%%] with '', does it work?

Where software development knowledge meets the reader

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-30 : 07:54:54

im sorry i dont have access to sql server 7

Where software development knowledge meets the reader

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources