May 24, 2011 at 12:39 pm
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...
May 24, 2011 at 12:53 pm
Please provide sample table structure.
Also, does the data appear exactly like that or is there other data in the string?
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
May 24, 2011 at 1:00 pm
A very basic example due to missing sample data:
DECLARE @x VARCHAR(50)
SET @x='Maryland<h2></h2>'
SELECT '<h2>'+ REPLACE(@x,'<h2></h2>','</h2>')
WHERE @x LIKE '%<h2></h2>'
May 24, 2011 at 1:08 pm
There are nine fields in the table.
That particular field - "teaser" -does contain other strings in most rows, but in those 50 rows the data is exactly as:
Maryland<h2></h2>
Maine<h2></h2>
Louisiana<h2></h2>
Kentucky<h2></h2>
Kansas<h2></h2>
Iowa<h2></h2>
...and so on... with nothing else in the field
"teaser" is type=longtext, null=no, default=none
May 24, 2011 at 1:13 pm
Thanks, Lutz
I can do the same thing with
UPDATE table set teaser=replace(teaser, '<h2></h2>', '</h2>')
to change
Maryland<h2></h2>
Maine<h2></h2>
etc....
to
Maryland</h2>
Maine</h2>
etc...
The second step of adding an opening <h2> tag to the beginning of the string in those 50 rows is really the problem...
May 24, 2011 at 1:18 pm
??????
Did you try to modify the solution I posted to work against your sample data?
May 24, 2011 at 1:19 pm
Look closer at Lutz's example. He is using a '<h2>' + replace(...) which will produce the results you are seeking.
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
May 24, 2011 at 1:22 pm
Yes, I'm trying it using wildcards - % - with no success so far
May 24, 2011 at 1:27 pm
I see that - but getting syntax errors
I appreciate the help, you guys...
May 24, 2011 at 1:31 pm
Ok, let's start from the beginning:
Please read and follow the instructions given in the first article referenced in my signature and provide ready to use sample data.
May 24, 2011 at 1:34 pm
vradul (5/24/2011)
I see that - but getting syntax errorsI appreciate the help, you guys...
Please post the query that you are using along with the syntax errors you are seeing.
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
May 24, 2011 at 2:14 pm
Thanks for trying guys - and sorry for the delay replying. I appreciate your time and efforts.
I got sidetracked putting out some other fires today - and just to save time I updated my 50 records manually.
May 24, 2011 at 2:16 pm
I think we would still like to see the queries you attempted with the errors you were getting.
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
May 24, 2011 at 2:22 pm
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
May 24, 2011 at 2:24 pm
Did I read the error correctly... "MySQL"???
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply