Help w/query to remove 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

General SQL Server Forums

New to SQL Server Programming

Help w/query to remove spaces

Author

Topic

sqlneve
Starting Member

16 Posts

Posted - 2008-11-07 : 11:14:44

I have several fields where apostrophes have been replaced with a space.

So Allison's Grocery Store is now Allison s Grocery Store
I need this to become Allisons Grocery Store

I have a query for it but I must have a mistake in it. Any help?

The query is as follows...

SELECT NAME,
REPLACE(NAME, '% S ', 'S ') AS NewNAME
FROM sdeALLSITES.Banks.LAALL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 11:16:44

no need of replacing with space, just replace with blank
REPLACE (NAME,'''','')

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-07 : 11:17:32

select replace('Allison s Grocery Store',' s','s')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 11:19:52

quote:
Originally posted by sakets_2000

select replace('Allison s Grocery Store',' s','s')

that'll be 'Allisions Grocerystore'

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 11:21:43

[code]maybe replace('Allison s Grocery Store',' s ','s ')[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-07 : 11:21:49

quote:
Originally posted by hanbingl

quote:
Originally posted by sakets_2000

select replace('Allison s Grocery Store',' s','s')

that'll be 'Allisions Grocerystore'

right,
Would this help ?

select replace('Allison s Grocery Store',' s ','s ')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 11:24:02

why get rid off apostrophes ...

sqlneve
Starting Member

16 Posts

Posted - 2008-11-07 : 11:33:14

quote:
Originally posted by hanbingl

why get rid off apostrophes ...

i didn't it was already done but i think it's because when i search through an attribute table i look for x = 'value' and when there is an apostrophe the selection would be x = 'value's' and the apostrophe isn't looked at correctly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 11:42:43

for that you should have just escaped it as

x='values''s'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources