replacing Apostrophe

I’m trying to key the syntax for replacing the Apostrophe with double
Apostrophe and update table.
Example: Programmer’s with Programmer’’s
Here code for finding occurrences:
SELECT DEALNAME
FROM DLWKTABLE20060609
where dealname like '%['']%'
BUT how do I update:
Update dlwktable20060609
Set dealname = replace(dealname, ?,?)
where dealname like '%['']%'declare @.c varchar(50)
select @.c ='O''Brian'
select replace(@.c, '''',''') ,@.c
Then the update will be
Update dlwktable20060609
Set dealname = replace(dealname, '''',''')
where dealname like '%['']%'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Logger wrote:
> I'm trying to key the syntax for replacing the Apostrophe with double
> Apostrophe and update table.
> Example: Programmer's with Programmer''s
> Here code for finding occurrences:
> SELECT DEALNAME
> FROM DLWKTABLE20060609
> where dealname like '%['']%'
> BUT how do I update:
> Update dlwktable20060609
> Set dealname = replace(dealname, ?,?)
> where dealname like '%['']%'

Replacement for my LIKE Clause

Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
[vbcol=seagreen]
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ?
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
[vbcol=seagreen]
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.
Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN ?
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
>
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
sql

replace value of (sq:variable("@variable")) with ""

replace value of(/node/sql:variable("@.varname")/@.Status)[1] with
"pass"'

gives me The XQuery syntax '/function()' is not supported.

Is it not supported?

If it is not supported what is the best way to update a specific node out of many based on certain criteria?

like update Student Grade to A for Student node with name "John" and not any other, without having to use dynamic SQL.

Tim

|||

reproducing answer to this by Pohwan on another discussion alias. This is exactly what I wanted and it works if somebody else is in is same boat

*****************

Workaround,

/node/*[local-name()=sql:variable("@.varname")]/@.Status

instead of

/node/sql:variable("@.varname")/@.Status

--
Pohwan Han. Seoul.

**************

|||

Thanks, that worked wonders for me

replace value of (sq:variable("@variable")) with ""

replace value of(/node/sql:variable("@.varname")/@.Status)[1] with
"pass"'

gives me The XQuery syntax '/function()' is not supported.

Is it not supported?

If it is not supported what is the best way to update a specific node out of many based on certain criteria?

like update Student Grade to A for Student node with name "John" and not any other, without having to use dynamic SQL.

Tim

|||

reproducing answer to this by Pohwan on another discussion alias. This is exactly what I wanted and it works if somebody else is in is same boat

*****************

Workaround,

/node/*[local-name()=sql:variable("@.varname")]/@.Status

instead of

/node/sql:variable("@.varname")/@.Status

--
Pohwan Han. Seoul.

**************

|||

Thanks, that worked wonders for me

replace T-SQL fonction

I would like to know how to use a replace fonction on a table
Like, currently, the syntax for replace is

SELECT REPLACE('abcdefghicde','cde','xxx')

but I would like to do something more like

SELECT REPLACE ((SELECT * FROM TABLE1) , 'cde', 'xxx')

Thanks to reply!I've found my problem ... which was in fact simple

it's:

select replace (FieldName , 'abc' , 'xxx') from MyTable...

the problem was that my FieldName was a Text field... which doesn't work... you need a varchar or nvarchar there...
so:

select replace (CAST(FieldName AS varchar(8000)), 'abc', 'xxx') from myTable

Thanks anyway!|||You should consider changing your fieldtype to VARCHAR if TEXT is not required. It will make coding easier and improve efficiency.

blindmansql

Replace syntax error

Hello

In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:

UPDATE CAT_Products

SET Description = replace (Description, '</h3>', '</h2>')
WHERE Description <> '</h2>'

UPDATE CAT_Products
SET Description = replace (Description, '<h3>', '<h2>')
WHERE Description <> '<h2>'

However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

What do I need to adjust?

Thanks

can you explain what you mean by not working? also pls provide some sample data you have that you are expecting the REPLACE to happen on.

|||

Hello ndinaker

When I put in this code:

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

I dose not execute and I get this error message instead:

[Error] Script lines: 1-5 --------
Argument data type ntext is invalid for argument 1 of replace function.

[Executed: 12/04/07 19:17:21 BST ] [Execution: 0/ms]

As in the text Description column, I am trying to update the <h3> and </h3> to <h2> and </h2> for example: <h3> Some Text </h3> to be replaced by <h2> Some Text </h2>

However, as it is written in html format in the DescriptionHTML database column it looks like:

<h3> Some Text </h 3> to be replaced by <h 2> Some Text </h2>

Thanks

|||

I think the pattern you are matching is only for <h3> and not </h3>. So you probably need to a double replace.

declare

@.str varchar(100)

set

@.str='<h3> Some Text </h3>'

--final value = <h 2> Some Text </h2>

select

@.str,replace(@.str,'<h3>','<h2>'),replace(replace(@.str,'<h3>','<h2>'),'</h3>','</h2>')

|||

Hi ndinakar

Thanks for the code, but I needed to change:

@.str varchar(100)

To

@.str ntext

H

owever this change cause the code to fail:

UPDATE CAT_Products

@.str ntext
set

@.str = '<h3> * </h3>'
--final value = <h 2> * </h2>

select

@.str, replace(@.str,'<h3>', '<h2>'), replace( replace(@.str,'<h3>', '<h2>'), '</h3>', '</h2>')

I got this error message:

[Error] Script lines: 1-13 --------
Line 4: Incorrect syntax near'@.str'.

More exceptions ... Must declare the variable'@.str'.

[Executed: 12/04/07 21:49:22 BST ] [Execution: 0/ms]

|||

Try if this works:

UPDATE

CAT_Products

set

DescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>')

WHERE

Description<>'<h2>'

|||

Before you try the update try a SELECT first to see what records are getting affected and how.

SELECT

NewDescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

As suggested, I tried this first:

SELECT

NewDescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>'),
DescriptionHTML

FROM

CAT_Products
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:40:42 BST ] [Execution: 0/ms]

Then I tried this:

UPDATE

CAT_Products
set

DescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>')
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:43:54 BST ] [Execution: 0/ms]

It looks like I will have to update this column manually, I had best roll up my sleeves.

Thanks

|||Ahhhh...its the NTEXT. Its going to be a pain to retrieve those values/update them through the query analyzer. Perhaps you can write a little tool..a VB tool with a form...that retrieves the values into a textbox and a button to update it..that will be easier..|||

Try this:

SELECT

NewDescriptionHTML

=replace(replace(convert(varchar(4000),DescriptionHTML),'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

Hi ndinakar

Thanks again for your help and support

Yes you are right the Ntext is a pain, cos when I tried your latest code suggestion I got this error:

[Error] Script lines: 1-15 --------
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 13/04/07 09:17:43 BST ] [Execution: 0/ms]

It certainly dose not like the NTEXT.

A cup of coffee and rolling up of the sleeves coming up.

Thanks

sql

REPLACE ISSUE..

Hi All,

I have the following STRING - 'D or Shorty'
Struggling with the syntax required to replace the 'or' with ; without
also replacing the 'or' in 'Shorty' with ;

e.g.

ORIG STRIMG:
D or Shorty

REQUIRED STRING:
D ; Shorty

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I have the following STRING - 'D or Shorty'
> Struggling with the syntax required to replace the 'or' with ; without
> also replacing the 'or' in 'Shorty' with ;
> e.g.
> ORIG STRIMG:
> D or Shorty
> REQUIRED STRING:
> D ; Shorty

SELECT replace('D or Shorty', ' or ', ' ; ')

I would guess that your real problem is somewhat more complex, but knowing
it, it's difficult to suggest a solution.

One should be aware of that SQL Server capabilities for string
manipulation is not extremely powerful, so depending on your task,
it could be an idea to bring it client-side and use something like
Perl to do whatever you want to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Mar 2005 13:30:37 -0800, hharry wrote:

> Hi All,
> I have the following STRING - 'D or Shorty'
> Struggling with the syntax required to replace the 'or' with ; without
> also replacing the 'or' in 'Shorty' with ;
> e.g.
> ORIG STRIMG:
> D or Shorty
> REQUIRED STRING:
> D ; Shorty
> Thanks in advance

DECLARE @.ss varchar(30)
SET @.ss = 'D or Shorty'
SELECT @.ss, REPLACE(@.ss, ' or ', ' ; ')

---------- --------------
D or Shorty D ; Shorty

(1 row(s) affected)|||Thanks all,

Yes, the issue is slightly more complex and I have went with this
option:

SELECT 'D or Shorty', SUBSTRING('D or Shorty', 1, CHARINDEX(' or ', 'D
or Shorty') - 1) + ' ; ' +
SUBSTRING('D or Shorty', (CHARINDEX(' or ', 'D or Shorty') + 4), LEN('D
or Shorty'))

Ross Presser wrote:
> On 15 Mar 2005 13:30:37 -0800, hharry wrote:
> > Hi All,
> > I have the following STRING - 'D or Shorty'
> > Struggling with the syntax required to replace the 'or' with ;
without
> > also replacing the 'or' in 'Shorty' with ;
> > e.g.
> > ORIG STRIMG:
> > D or Shorty
> > REQUIRED STRING:
> > D ; Shorty
> > Thanks in advance
> DECLARE @.ss varchar(30)
> SET @.ss = 'D or Shorty'
> SELECT @.ss, REPLACE(@.ss, ' or ', ' ; ')

> ----------
--------------
> D or Shorty D ; Shorty
> (1 row(s) affected)|||On 15 Mar 2005 14:47:56 -0800, hharry wrote:

> Thanks all,
> Yes, the issue is slightly more complex and I have went with this
> option:
> SELECT 'D or Shorty', SUBSTRING('D or Shorty', 1, CHARINDEX(' or ', 'D
> or Shorty') - 1) + ' ; ' +
> SUBSTRING('D or Shorty', (CHARINDEX(' or ', 'D or Shorty') + 4), LEN('D
> or Shorty'))

If your issue will come up in a similar way in the future, it might be
worth it to encapsulate that "replace first occurence only" into a UDF.

CREATE FUNCTION ReplaceFirstOccurence
( @.src varchar(8000), @.sub varchar(8000), @.repl varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.n int
SET @.n = CHARINDEX(@.sub, @.src)
RETURN SUBSTRING(@.src, 1, @.n-1)
+ @.repl
+ SUBSTRING(@.src, @.n + LEN(@.sub) + 1,8000)
END

A much crazier idea that I just discovered, while googling on this topic,
is to pull in the VBScript regular expression library!

http://www.sqlteam.com/item.asp?ItemID=13947

They only demonstrate using it for testing a string against a regexp, but
it could easily be extended to use replace.

Replace data in SQL server table column

What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.Originally posted by josephjthomas
What is the correct syntax to replace a field data nvarchar(50)

Current data = 0020-10-02
Change = 2003-10-02

Thank you in advance.

Update table_name
set (column_name='2003-10-02)
where (select * from table_name where column_name='0020-10-02')|||Why do you assume it's 2003?

DECLARE @.x varchar(50), @.y varchar(50)
SELECT @.x = '0020-10-02'

SELECT SUBSTRING(@.x,3,2)+'03'+RIGHT(@.x,6)|||It's definitely 2003. The entry was mis-typed.

I tried the Update query but that didn't work. Even adding the missing hypen.

Brett,

If I try what you posted, how does it process the update?|||USE Northwind
GO

CREATE TABLE myTable99(x varchar(50))
GO

INSERT INTO myTable99(x)
SELECT '0020-10-02' UNION ALL
SELECT '2004-10-02'
GO

SELECT * FROM myTable99

UPDATE myTable99
SET x = SUBSTRING(x,3,2)+'03'+RIGHT(x,6)
WHERE x = '0020-10-02'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Or can just had code it in the set|||Originally posted by josephjthomas
It's definitely 2003. The entry was mis-typed.

I tried the Update query but that didn't work. Even adding the missing hypen.

Brett,

If I try what you posted, how does it process the update?
My bad:

UPDATE table_name
SET column_name = '2003-10-02'
WHERE EXISTS
(SELECT *
FROM table_name
WHERE column_name = '0020-10-02')|||That worked great! Thank you!|||Originally posted by Ida Hoe
My bad:

UPDATE table_name
SET column_name = '2003-10-02'
WHERE EXISTS
(SELECT *
FROM table_name
WHERE column_name = '0020-10-02')

Where's the coorelation...

Why not a simple WHERE?

That'll update all rows...I was suprised it ran...

USE Northwind
GO

CREATE TABLE myTable99(x varchar(50))
GO

INSERT INTO myTable99(x)
SELECT '0020-10-02' UNION ALL
SELECT '2004-10-02'
GO

SELECT * FROM myTable99

UPDATE myTable99
SET x = '2003-10-02'
WHERE EXISTS ( SELECT *
FROM myTable99
WHERE x = '0020-10-02')

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Sorry. I meant to say Brett's script ran and not Ida Hoe.

Thank you both. :-)

Replace blank fields

Hi
I am replacing null and blank fields in a table.
Does anyone know the syntax to replace a blank field?
Update site
Set postcode = '-'
Where postcode is nullOn Fri, 11 Feb 2005 06:37:05 -0800, Jaco wrote:

>Hi
>I am replacing null and blank fields in a table.
>Does anyone know the syntax to replace a blank field?
>Update site
>Set postcode = '-'
>Where postcode is null
Hi Jaco,
UPDATE site
SET postcode = '-'
WHERE postcode IS NULL
OR postcode = ''
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Repl failure on <> in data field

We added data to a table with ‘<’ and ‘>’ symbols. The replication failed on
SQLServer with the error “Syntax error or access violation”. We replaced the
'<' and '>' with '(' and ')', which fixed the replication error. Can you not
use brackets in a table that is replicated?
This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
" and '
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is this a known bug?
Is there any fix available?
"Paul Ibison" wrote:

> This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
> " and '
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||You could describe it as a limitation, a feature or a bug
There's no fix - apart from removing the relevant characters (see
http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry I did not expained well.
The table name doesn't contain special character but a space. (Space is not
in the list of special characters). For example [Title Types]
Replication works fine. Adding new data, updating data gets replicated
successfully. Now, when you add data with any special characters in one of
the nvarchar data fields of replicated table ( i.e. [Title Types]).
Replication fails.
The microsoft article addressing the issue of article name with special
characters but I have issue with data when table name contains space(which is
not special character listed).
I can not stop users to not enter special characters in data. So I think I
have only 2 options:
1. Rename table to TitleTypes (i.e remove space), All special characters
will work in data. (Lot of work as table is getting referenced from many
places)
2. add instead of trigger on table to get rid of special characters. (Extra
load on DB for insert and update due to trigger).
Further suggestions will be helpful.
Thanks
"Paul Ibison" wrote:

> You could describe it as a limitation, a feature or a bug
> There's no fix - apart from removing the relevant characters (see
> http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>