Need to repace spaces with dashes

[mds840]

I am looking to replace spaces between words in a field with dashes "-"

Example:

From: "See it here"
To: "See-it-here"

Should be easy but I cannot seem to find the syntax.


Thanks for any help.

[WebJoel]

use either − or &8722; to produce the 'minus' sign in technical characters form.
-Joel

[NogDog]

Are you looking to change the spaces to dashes when you insert the data into the database, or just when displaying the data after retrieving it from the database? (In either case, it would help to also tell us what DBMS you are using and what programming language.)

[chazzy]

almost every dbms will have some kind of strreplace, replace type of function which usually takes parameters:
replace('search_for','replace_with','in_this_string')

it really has nothing to do with what programming language you use.

[mds840]

I will be using PHP and SQL

What I am doing is taking a Table entry from the DB and using it as a website link.

Straight from the table would show: www.mysite.com/see it here/
It should look like: www.mysite.com/see-it-here/

When it displays now, as I have it, it comes out: www.mysite.com/see

So I need the SQL / PHP code to get it to add the dashes.
Hope this explains it

[chazzy]

what do you mean by SQL? SQL stands for "structured query language" it's a language. it behaves differently with each DBMS though. I think you mean "MySQL" though.


REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

This function is multi-byte safe.


so you can use REPLACE(`column_name`,' ','-') in your query.

[mds840]

OK I am acting like I have never done this before.
I have tried it many different ways getting the error below or a parsing error.

Fatal error: Call to undefined function: replace() in /html/index.php on line 322

Line 322 is the replace line

I put the code below:


print "<!-- SQL: $sql -->";
$result = mysql_query($sql);
$rowcount = mysql_num_rows($result);
while ($array = mysql_fetch_array($result, MYSQL_BOTH)) {
print "<span class=content>";
print "<b>";
print "<img src=http://www.mysite.com/images/listitem.png> ";
$array[article_title] = Replace($array[article_title], " ", "-");
print "<a href=http://www.mysite.com/article/"."Article"."/"."$array[article_title]"."/"."$array[article_id]".">";
print "$array[article_title]";
print "</a>";
print "</b>";
print "<br>";
print "</span>";
}

[chazzy]

it's not a php function, its a mysql function

your $sql would be like this:


SELECT column_list,..., REPLACE(`article_title`,' ','-') as rep_article_title FROM your table....


it's important to use single quotes as well.

you need to use $array['rep_article_title'] to reference the column.

[NogDog]

it really has nothing to do with what programming language you use.
It does if you want to do the search/replace within the program instead of within the query. I'm not saying either way is "better", just that there are two ways to approach it. (You might want the script to support several DBMS's so don't want to depend on a query function that's not part of standard SQL, perhaps?)

For instance:

print "<a href='http://www.mysite.com/article/Article/" .
str_replace(" ", "-", $array[article_title]). "/" .$array[article_id] . "'>";

[chazzy]

actually, a string replace function is part of the standard.

and even if you wanted to search outside of the database (i will never know why you'd want to do that...) you can still search in these terms, and you'd have to convert the spaces in your search w/ dashes - but for some reason I don't see that being any more useful.

[mds840]

Thanks guys for all your help, NogDog that did the trick.
Chazzy your suggestions worked great if I was doing everything in SQL but I needed it to be part of my app.

To see what I mean you can go to http://www.wahcafe.com and click on the "Test Article" link under the Most Popular Articles heading.

If you look at the Newest Articles heading below it you can see how it was working for me.

The site is still in progress so some of the links dont work.


Thanks again guys.

[chazzy]

Just wondering, but can you detail the benefits of doing it this way?

A sql statement like this would do the same thing, and you would only need to reference one column to build this

SELECT concat('<a href=\"http://www.mysite.com/article/Article/\"',REPLACE(`article_title`,' ','-'),'\"/\"',`article_id`) as article_link FROM a_articles ORDER BY article_id;

I'd test it, but I don't have my db near me.

[mds840]

I need to do it that way so that I can display the TITLE as a link on the site without dashes. But the link to the actual article needs to have dashes in it when displayed in the address bar since spaces are not allowed in the URL.

Your way may work but since I am integrating another app I need to go around their code. I dont want to add anymore fields to the other app since it is much larger than the entry page that I am creating for it.

MOST POPULAR
---------------
Test Article <--- TITLE as shown in table

When dispayed in address bar:
http://www.wahcafe.com/articles/Article/Test-Article/2

Also I am not ordering by article number, it is by Most Hits.
I am not sure exactly why the original code was written to display this way, all I can think of is to make it SE friendly and to allow for duplicates of article titles.

[chazzy]

at the bare minimum you can just select both columns, you actually don't get a performance hit. typical sql functions run faster, since they can deal with the raw data. I'm not sure how you'll see it between MySQL and php computing it

select other_columns, REPLACE(`article_title`,' ','-') article_title_dash, article_title FROM your_table; and you would refer to the column article_title_dash.

Don't misjudge SQL by any means - you can write an entire application with just sql statements, using a server side language to display the output and parse passed parameters. Oracle's PL/SQL can actually take the place of a server side language - it's a derivative of Perl that can interact with a database directly.