I need help removing a ' from a field
Here is an example that should get you started:
create table #sometable (somecolumn varchar(80))
insert into #sometable (somecolumn) values ('lets try to remove the '' =
additional text'' within this column')
insert into #sometable (somecolumn) values ('lets try to remove the '''' =
additional text'''' within this column')
select * from #sometable=20
GO
UPDATE #sometable SET somecolumn =3D REPLACE(somecolumn, '''', '')
select * from #sometable=20
--=20
Keith
"Jon Hirasaki" <jhirasaki@.homecaresupply.com> wrote in message =
news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...
> I need help removing a ' from a field
|||I used the update command
update table set name = replace (name,'''','')
and it puts a blank space in the field where the ' was. How do I remove the blank space? I other blank spaces in the field that I need to keep.
"Keith Kratochvil" wrote:
> Here is an example that should get you started:
>
> create table #sometable (somecolumn varchar(80))
> insert into #sometable (somecolumn) values ('lets try to remove the '' additional text'' within this column')
> insert into #sometable (somecolumn) values ('lets try to remove the '''' additional text'''' within this column')
> select * from #sometable
> GO
> UPDATE #sometable SET somecolumn = REPLACE(somecolumn, '''', '')
> select * from #sometable
>
> --
> Keith
>
> "Jon Hirasaki" <jhirasaki@.homecaresupply.com> wrote in message news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...
>
|||I am not sure what you mean. The update sets this value:
lets try to remove the ' additional text' within this column
To this:
lets try to remove the additional text within this column
As you can see, the apostrophe after "text" has been removed and =
replaced with nothing. There is still one space between "text" and =
"width."
The apostrophe after "the" was removed and replaced with nothing, =
however there are two spaces between "the" and "additional" because =
there were two spaces between the original string "the ' additional."
The update works as expected...the layout of the original data causes =
the end result to look a bit strange.
Do you have two spaces when you only want one? If so, try this:
UPDATE #sometable SET somecolumn =3D REPLACE(somecolumn, ' ', ' ')
select * from #sometable=20
--=20
Keith
"Jon Hirasaki" <Jon Hirasaki@.discussions.microsoft.com> wrote in message =
news:D804BB8F-C783-4D63-8DF0-0EC2BBAE69FE@.microsoft.com...
> I used the update command
> update table set name =3D replace (name,'''','')
> and it puts a blank space in the field where the ' was. How do I =
remove the blank space? I other blank spaces in the field that I need =
to keep.[vbcol=seagreen]
>=20
> "Keith Kratochvil" wrote:
>=20
'' additional text'' within this column')[vbcol=seagreen]
'''' additional text'''' within this column')[vbcol=seagreen]
news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...[vbcol=seagreen]
|||What I have is value: let's try to remove the additional text within this column
I run: update #sometable set somecolumn= replace(somecolumn, '''', ' ')
This code removes the ' but it leave a blank space so the value look like this:
let s try to remove the additional text within this column
"Keith Kratochvil" wrote:
> I am not sure what you mean. The update sets this value:
> lets try to remove the ' additional text' within this column
> To this:
> lets try to remove the additional text within this column
> As you can see, the apostrophe after "text" has been removed and replaced with nothing. There is still one space between "text" and "width."
> The apostrophe after "the" was removed and replaced with nothing, however there are two spaces between "the" and "additional" because there were two spaces between the original string "the ' additional."
> The update works as expected...the layout of the original data causes the end result to look a bit strange.
> Do you have two spaces when you only want one? If so, try this:
> UPDATE #sometable SET somecolumn = REPLACE(somecolumn, ' ', ' ')
> select * from #sometable
> --
> Keith
>
> "Jon Hirasaki" <Jon Hirasaki@.discussions.microsoft.com> wrote in message news:D804BB8F-C783-4D63-8DF0-0EC2BBAE69FE@.microsoft.com...
>
|||Try removing the space from the "replace" character (as I had done =
within my original sql statement):
update #sometable set somecolumn=3D replace(somecolumn, '''', '')
--=20
Keith
"Jon Hirasaki" <Jon Hirasaki@.discussions.microsoft.com> wrote in message =
news:F41CC89E-0926-4D24-B624-1052A48DD57B@.microsoft.com...
> What I have is value: let's try to remove the additional text within =
this column
> I run: update #sometable set somecolumn=3D replace(somecolumn, '''', ' =
')
> This code removes the ' but it leave a blank space so the value look =
like this:[vbcol=seagreen]
> let s try to remove the additional text within this column
>=20
>=20
>=20
> "Keith Kratochvil" wrote:
>=20
replaced with nothing. There is still one space between "text" and =
"width."[vbcol=seagreen]
however there are two spaces between "the" and "additional" because =
there were two spaces between the original string "the ' additional."[vbcol=seagreen]
causes the end result to look a bit strange.[vbcol=seagreen]
message news:D804BB8F-C783-4D63-8DF0-0EC2BBAE69FE@.microsoft.com...[vbcol=seagreen]
remove the blank space? I other blank spaces in the field that I need =
to keep.[vbcol=seagreen]
the '' additional text'' within this column')[vbcol=seagreen]
the '''' additional text'''' within this column')[vbcol=seagreen]
'')[vbcol=seagreen]
news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...[vbcol=seagreen]
sql
