CODEWITHSUNDEEP
mysqlsql
SUN
SUN

Reputation: 973

adding dash between spaces in field name in mysql

I have database in mysql & have maintain 3 columns i.e. ID , name, slug.

Slug column is same as name but here I put combination of name column. Suppose if name is Micky Mehta then in slug I want it should show micky-mehta. In slug I want everything in small letters & if space then it should be replaced with dash. All this because I want SEO friendly url for my site. Now I can do this with program when new products gets added but is it also possible If I can directly do that with mySQL? I mean is there any function in mySQL which perform this kind of task?

Upvotes: 2

Views: 1457

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562270

You can update data to fit your scheme:

UPDATE mytable SET slug = REPLACE(LOWER(name), ' ', '-');

If you want it to happen automatically every time you add a new row or update an existing row, you can use triggers:

CREATE TRIGGER SetSlugIns BEFORE INSERT ON mytable
  FOR EACH ROW
  SET NEW.slug = REPLACE(LOWER(NEW.name), ' ', '-');

CREATE TRIGGER SetSlugUpd BEFORE UPDATE ON mytable
  FOR EACH ROW
  SET NEW.slug = REPLACE(LOWER(NEW.name), ' ', '-');

In MySQL 5.7, they now have generated columns, so you can do the same thing without writing triggers:

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  slug VARCHAR(100) AS (REPLACE(LOWER(name), ' ', '-')
);

Upvotes: 3

prograshid
prograshid

Reputation: 927

You can also do this with your programming language. If you are coding in php you can do it as below Take a new variable say $slug

$name='Jone Doe'; //the name value you getting from form or input

//convert string into lower. It will give you 'jon doe'
$nameInLower = strtolower($name); 

// now replace space with hyphen.
$slug=str_replace(' ', '-',$nameInLower);

Insert the $slug variable in your database table along with the name column(considering id is auto incremented column.

Upvotes: 0

shubham715
shubham715

Reputation: 3302

Try This example-

UPDATE yourTable SET yourColumn = LOWER(replace('Your Value', ' ', '_'))

Upvotes: 0

Related Questions