Hi, I just can't figure how to do this with an SQL statement.

I want to replace all the '&' with 'and' in my category titles. Example 'Sports & Recreation' becomes 'Sports and Recreation'. I need to do this because my hyperlinks to thier directorires do not recognize the &.

The part the beats me is how to find and replace only the '&' part of the title.

UPDATE lsql_Category SET Title=.................................. HELP!

I would personally do this using Perl. That is, I would loop through each row, read the row and do a $_ =~ /&/and/g; before re-inserting the information.

I'm sure this can be done using pure SQL - I think you might be able to do this using some of the regex features? I'm not sure.

- wil

You don't really want perl much too much effort....

UPDATE lsql_Category SET Title = REPLACE(Title, '&', 'and')

Are you sure of the syntax of your previous message? The following page doesn't match up with what the code you supplied:

http://www.mysql.com/doc/R/E/REPLACE.html

- wil

Yes Im sure ;)

Hm. OK. They just don't specify UPDATE anywhere, but saying that you should start the command with REPLACE ...

- wil

Mmm no you shouldn't. The way I have it is fine.

You may also wanna replace & with & wouldn't you? I always thought Links SQL changed it to that upon submission into the SQL database?

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Mmm & isn't being submitted into the database anyway..."and" is

...either way the answer is still no :)

???

>>>I want to replace all the '&' with 'and' in my category titles. <<<

Is it me, or does he specifically say he wants to replace ALL &'s with the word 'and', not the other way, as you are saying Paul ;)

I know it is wrong..but I don't like having people telling me I'm wrong for something, when I don't actually think I am

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

>>
Is it me, or does he specifically say he wants to replace ALL &'s with the word 'and', not the other way, as you are saying Paul ;)
<<

No thats the opposite of what I was saying you must have misread my post or something.

Lets look at what I said:


I said "and" is being inserted in to the database which is what Philip asked for...get rid of &, replace with "and"

&amp; isn't required whether you are replacing & with and or vice versa.

Thanks ALL! I never expected to see so many replies and discussions on this topic when I got up this morning..... fantastic!

Thanks for the statement Paul, SQL is becoming less mysterious to me every day.

Ian. (who's phillip?) just kidding, I always get a kick when you veterans are discussing back and forth.

THANKS AGAIN

Hmm thats too weird, I could have sworn "sponge" had asked the question...thats why I said "Philip" above...hmmm. I must need glasses :)

That's the wrong REPLACE, Paul's using the replace() function, not the REPLACE sql statement. See:

http://www.mysql.com/...tring_functions.html

Cheers,

Alex
--
Gossamer Threads Inc.

Aha. Thanks for the clarification.

On a seperate note; why does [quote] take out the URL markup?

Cheers

- wil

In my post it didn't because I was using mozilla and the regular editor and it didn't auto link up the URL like IE does.

Cheers,

Alex
--
Gossamer Threads Inc.

OK. I was referring to why it would strip [url] tag from the OP. Never mind!

- wil