What should be a simple query needed

Question

Post reply

What should be a simple query needed

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

vradul SSC Rookie Points: 26 More actions · Answer 1

yes - is the syntax different? I was under the impression it is the same...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

vradul (5/24/2011)
yes - is the syntax different? I was under the impression it is the same...

Some syntax is, but each product has certain things that are particular to that version of SQL.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

vradul (5/24/2011)
I copied/pasted Lutz's query, adding * FROM 'table' to the SELECT...
DECLARE @x VARCHAR(50)
SET @x='Maryland<h2></h2>'
SELECT * FROM 'table' '<h2>'+ REPLACE(@x,'<h2></h2>','</h2>')
WHERE @x LIKE '%<h2></h2>'
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @x VARCHAR(50) SET @x='Maryland<h2></h2>' SELECT * FROM 'table' '<h2>'' at line 1

Your syntax is incorrect as the message states. The modification you made to the table creates the wrong syntax.

try this.

DECLARE @x VARCHAR(50)

SET @x='Maryland<h2></h2>'

SELECT * , '<h2>'+ REPLACE(@x,'<h2></h2>','</h2>') --move the from table from this line and added a comma after the *

FROM 'table' --move the from table to this line.

WHERE @x LIKE '%<h2></h2>'

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

vradul SSC Rookie Points: 26 More actions · Answer 4

Thanks....

DECLARE @x VARCHAR(50)

SET @x='Maryland<h2></h2>'

SELECT * , '<h2>'+ REPLACE(@x,'<h2></h2>','</h2>')

FROM 'node_revisions'

WHERE @x LIKE '%<h2></h2>'

gives me syntax error still...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

remove the quotes from around your table name. Also, replace the variable in the replace statement with the column name from the table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 6

Did you realize the @x actually is a variable and not a column name?

Since we still don't know anything about your table structure we can't provide the correct (SQL Server) query. The fact that it is MySQL and not SQL Server doesn't help either. It might have helped if you would have mentioned it up front. But that's water down the river...

As a side note: a quick google search for REPLACE and MySQL seems to indicate that the functionality is different between the two database systems.

You might get more help on a MySQL forum, I'd say.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

vradul (5/24/2011)
I have a table named “table” containing a few hundred rows with a field named "teaser" that contains various text strings, among which are 50 rows with “teaser” containing the following:
Maryland<h2></h2>
Maine<h2></h2>
Louisiana<h2></h2>
Kentucky<h2></h2>
Kansas<h2></h2>
Iowa<h2></h2>
...and so on...
I need a query to target those 50 rows to rewrite "teaser" like this in only those 50 rows:
<h2>Maryland</h2>
<h2>Maine</h2>
<h2>Louisiana</h2>
<h2>Kentucky</h2>
<h2>Kansas</h2>
<h2>Iowa</h2>
...and so on...
while leaving all other rows unchanged...

This can be done quite simply using REPLACE, a single concatenation, and a WHERE clause with a LIKE. Give it a try. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vradul SSC Rookie Points: 26 More actions · Answer 8

solved for mysql with:

UPDATE mytable SET field=CONCAT('<h2>' , REPLACE(field,'<h2></h2>','</h2>'))

WHERE field LIKE '%<h2></h2>'

................

for sql this might work:

UPDATE mytable SET field=('<h2>' + REPLACE(field,'<h2></h2>','</h2>'))

WHERE field LIKE '%<h2></h2>'

Thanks for the help, guys!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Jeff Moden (5/24/2011)
This can be done quite simply using REPLACE, a single concatenation, and a WHERE clause with a LIKE. Give it a try. 😉

How embarrassing. I "missed" the other dozen or so posts after the original. I should stay in bed today. My apologies... :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)