replace '&' with '%20' in query output

Anon

Esteemed friends and brothers
and sisters in SQL,

How do I replace a character like '&' with its equivalent
character code '%26' in the output of a query.

I am outputting values directly from SQL to Flash Generator which reads the '&' character as a variable identifier.
Much like PHP uses the '$' character.
So the value 'Fitch & Mather' goes all the hell unless its output as Fitch %20 Mather'.

While your at it... tell me who we should appoint as benevolent dictator since it looks like we will be without a working democracy for a while...

~ OK

Anon

rawurlencode() and htmlentities() will do this. Whether they do too much encoding for your purpose is a different question...

Anon

Hi Kirk,

Like this:

SELECT RAWURLENCODE(File_Name) AS Name
FROM FILES

doesn't work
?

Anon

Wouldn't str_replace be a good function for handling this?

Anon

Except I need to do this IN mysql not in PHP.
The query is called thru Flash - not a webpage.

So my thinking was - just like I can do:

SELECT
DATE_FORMAT(FILES.Date_Modified,'%Y/%m/%d') AS File_Date

I could do some kind of
FORMAT(Field_Name.'asci') as Field_Name

seems logical but... can't seem to find a way to do it...

Thanks though. More thoughts?

~ OK

Anon

Patrick Locke wrote:

Hi Kirk,

Like this:

SELECT RAWURLENCODE(File_Name) AS Name
FROM FILES

doesn't work
?

That's because rawurlencode is a php function, not a database function. I.e. you have to do it from wihtin php like so:

$result=run_query("select ".rawurlencode($url)." as Name from files");

i.e. this data needs to be massaged ahead of time to use rawurlencode, but I got the feeling from other posts he didn't want to do that.

Since he's using mysql, and I don't use it for jobs like that, I'm not sure what to do. It's doable with a view though. If MySQL has working views in beta, then it might be worth the time to test out a beta mysql and see if it will work with a view.

Anon

I don't think MySQL is going to help you out here.

You may have to create an extra field in your table to hold the flash-specific URL.

You could easily put together a one-off PHP script to populate this field, change your input scripts to populate the new field too.

You can then pass the flash-specific URL string to your flash app.

Not very clever, but it would work!

Cheers,

Jack

(Free PHP hosting till the end of the year - www.combustion.ie)